Copy Paste Filtered Row using VBA

mrbuttons · Mar 31st 2021

Hello everyone,

I'm working on a simple automation to copy paste value from a worksheet to another worksheet. I'm not really an expert in VBA and I'm currently stuck in copying the first row of worksheet after the columns have been filtered.

As you can see below, I would like to copy the first row below to another worksheet and in a specific cells.

*To a different worksheet and in specific cells*

the VBA code I'm using is a standard code as per below.

Code

Sub Copy_First_Row()


'First step: Once filter, copy the cell respectively to another cell in another worksheet


Sheets("Hospital Info").Range("A2").Copy Destination:=Sheets("template").Range("B9")
Sheets("Hospital Info").Range("B2").Copy Destination:=Sheets("template").Range("B10")
Sheets("Hospital Info").Range("C2").Copy Destination:=Sheets("template").Range("B11")
Sheets("Hospital Info").Range("D2").Copy Destination:=Sheets("template").Range("B12")
Sheets("Hospital Info").Range("E2").Copy Destination:=Sheets("template").Range("B14")
Sheets("Hospital Info").Range("F2").Copy Destination:=Sheets("template").Range("B15")


Sheets("Product List").Range("A2").Copy Destination:=Sheets("template").Range("B20")
Sheets("Product List").Range("B2").Copy Destination:=Sheets("template").Range("C20")


End Sub

Display More

How can I add to take the filtered first row only and the copy paste it to the other worksheet if we are using my code above?

Thanks!

**Carim** · Mar 31st 2021

Hello,

Thanks a lot for your sample file ...

Could you clarify your process ...

The sheet Template is your Destination Sheet ... and both Hospital Info and Product List are your Source Sheets ...

But where are the criteria you want to use to filter .... and once rows are filtered in both the source sheets ... what is the exact destination in the Template sheet ...?

**Carim** · Mar 31st 2021

Hello again,

Attached are two examples in your Test file ...

Hope this will help

mrbuttons · Mar 31st 2021

Hi Carim

Yes correct, sheet Template is my destination sheet whereas Hospital Info and Product Lists are the source sheets.

When I filter based upon the first column (Address Line 1), the respective cells from the first row should be copied from one worksheet to another.

That means, first cell of Column A in worksheet Hospital Info should be copied and pasted to Column A cell 9 in worksheet Template.

This is also the same for other cells as per the VBA code.

Sheets("Hospital Info").Range("A2").Copy Destination:=Sheets("template").Range("B9")

Sheets("Hospital Info").Range("B2").Copy Destination:=Sheets("template").Range("B10")

Sheets("Hospital Info").Range("C2").Copy Destination:=Sheets("template").Range("B11")

Sheets("Hospital Info").Range("D2").Copy Destination:=Sheets("template").Range("B12")

Sheets("Hospital Info").Range("E2").Copy Destination:=Sheets("template").Range("B14")

Sheets("Hospital Info").Range("F2").Copy Destination:=Sheets("template").Range("B15")

Sheets("Product List").Range("A2").Copy Destination:=Sheets("template").Range("B20")

Sheets("Product List").Range("B2").Copy Destination:=Sheets("template").Range("C20")

Am I confusing you? hahaha

**Carim** · Mar 31st 2021

Quote from mrbuttons

Am I confusing you? hahaha

YES ... COMPLETETY ... !!!

Why don't you take advantage of the Test File ... to show your expected result with a basic example ...

mrbuttons · Apr 1st 2021

Hi Carim

The test file is brilliant! With your help, I managed to get the result needed.

Thank you so much!

**Carim** · Apr 1st 2021

Glad you could fix your problem

Thanks for your Thanks

**royUK** · Apr 1st 2021

You can make the code recognises that data has been added or removed by using CurrentRegion to define the range to be filtered. the CurrentRegion property in Excel VBA defines a range bounded by any combination of blank rows and blank columns.

Code

Sub FilterHospitalInfo()
    With Sheet1.Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:=Sheet1.Cells(2, 11)
        Sheet57.Range("A1").CurrentRegion.Clear
        .Copy Sheet57.Range("A1")
        .Range("A1").AutoFilter Field:=1
    End With
End Sub

Sub FilterProductList()
    With Sheet2.Range("A1").currenetregion
        .AutoFilter Field:=1, Criteria1:=Sheet1.Cells(2, 7)
        Sheet57.Range("A1").CurrentRegion.Clear
        .Copy Sheet57.Range("I1")
        .AutoFilter Field:=1
    End With
End Sub

''/// run the codes together
Sub FilterAll()
    FilterHospitalInfo
    FilterProductList
End Sub

Display More

mrbuttons · Apr 5th 2021

Thanks royUK !

**royUK** · Apr 5th 2021

Pleased to help.

Post back if you need further help.

Visit my web site, www.excel-it.com, for more examples and some helpful articles.

Copy Paste Filtered Row using VBA

Participate now!

Share

Tags