VBA Code to work across mutli sheet in one workbook

  • I'm working in excel with large data input with rows tallying more then 50K across two working sheets

    What I'm trying to do with no luck regards to the below highlighted rows, is to have a formula or somewhat if Column "Z" indicates a Y (Yes) how can I have an automatic copy of the columns, "A", "B", "D", "H", "I", "J", "K" "S" from Sheet1 (rows up to 39999K) and Sheet2 (rows up to 50K plus) and pasted into a Sheet3 being columns A,B,C,D,E,F,G,H - please see below snippet sheets.

    And if I delete the "Y" in column "Z" it will automatically delete from sheet3?

    Sheet 1

    [Blocked Image: https://filestore.community.su…-7c25080c3776?upload=true]


    [Blocked Image: https://filestore.community.su…-7c25080c3776?upload=true]


    [Blocked Image: https://filestore.community.su…-0901cb40507c?upload=true]

    Thank you in advance,


  • Hi HE,

    Your example sheet does not appear to make sense with reference to your question, for example "Date Completed" appears as a field in sheet 3 as column B but column Q from sheet 1 is not one of the columns you are trying to copy from sheet1 (according to your question). Additionally your complaint numbers on sheet2 in no way align with the complaint numbers on sheet1.

    I think I know what you are trying to do, i.e. copy the relevant information for a particular complaint from sheet1 to sheet 3 if there is a line Y in sheet 2 column Z but this is not really clear from the your post.

    This code in the sheet should work to some extent if modified to suit.

    As in the attachment it assumes a unique complaint ID, which may or may not be correct?

  • cheers man, I've had this code:

    Private Sub Worksheet_Activate()
    Worksheets("Sheet1").Range("A1").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("J1:J2"), _
    End Sub

    It works perfectly but I wasn't sure how to make it work on sheet2 as well. only sheet one appeared in sheet3 whenever "Y" was referenced. the example was pretty much whipped up on the spot, data in row 1 is correct.

    I will try the code provided.


  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.