Copy Paste Filtered Row using VBA

  • 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.

    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?


  • 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 ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • 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 ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!