Excel Question: How to copy filtered data to another filtered sheet

HIROYUKI TAMURA
HIROYUKI TAMURA used Ask the Experts™
on
I have two files
I want to open book1.xlsx and copy filtered data and paste it to book2.xlsx without changing BBB data.
book1.xlsx
Book2.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Try this, it assumes both workbooks are in the same folder.

Option Explicit

Sub CopyToNewWorkBook()
    Dim rng As Range
    Dim ws As Worksheet
    Dim ToWb As Workbook
    Dim fName As String
    ''///do some checks
    If ws Is Nothing Then Set ws = ActiveSheet
    ''///check whether a filter Is applied
    With ws
        If Not .AutoFilterMode Then
            MsgBox "sheet not filtered", vbCritical, "Quitting"
            Exit Sub
        End If
        
        If Not .FilterMode Then
            MsgBox "sheet has filters, but not filtered", vbCritical, "Quitting"
            Exit Sub
        End If

        ''///set a range = to visible cells (excluding the  header)
        Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1)
    End With
    Set ToWb = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Book2.xlsx")

    With ToWb
        rng.Copy .Sheet1.Cells(1, Rows.Count).End(xlUp).Offset(1)
        ''/// close & save
        .Close True
    End With

End Sub

Open in new window

Author

Commented:
Thank you, Roy Cox!
I'm trying to run the script, but not sure how to do it.
Am I doing the right way?

snap205.png
Roy CoxGroup Finance Manager

Commented:
I'll have a look at your examples and add the code to see if that helps you
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
thank you, Roy. I am unsure how to run the script.
Roy CoxGroup Finance Manager

Commented:
I'm busy at work today, but I'll amend it as soon as I can
Roy CoxGroup Finance Manager

Commented:
I think I misunderstood your question.

I've edited the code  and put it in Book2.  It will open Book1 and copy the filtered data to Book2.

Both workbooks need to be in the same folder.

The code is run by pressing the button in Book2.xlsm

Let me know if it needs adjusting
Book2.xlsm

Author

Commented:
Thank you, Roy. I have tried, but the data has not changed. snap213.png
Group Finance Manager
Commented:
It works for me. The code copies the filtered data form Book1 to the next empty row in Book2.

 What are you expecting?
Roy CoxGroup Finance Manager

Commented:
Is it working for you now?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial