Codes working for 2 templates but not the 3rd

[zenjah]

I have three templates that basically do the same thing - copy information from source spreadsheets and paste into a master spreadsheet using very similar formula

Two of them work fine but the third one only copies and pastes 1 row from each source spreadsheet for some reason - the codes used are identical to one another apart from the references

Is someone able to tell me what might be causing this from the codes below?

Thanks for your help

            'Copy data
              wb.Sheets("***").Columns("A:Z").EntireColumn.Hidden = False
              With wb.Sheets("***")
                .Range("A2", .Range("Z2").End(xlDown)).Copy
              End With
            
            'Paste data
              Windows("*** Master.xlsm").Activate
              Sheets("***").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[p45cal]

It's the arrangement of data on the sheet it's copying from that is going to be the problem.
.Range("Z2").End(xlDown) is going to give different results; if Z2 has data but Z3 not then only 2 rows are going to be copied. If Z2 has no data but Z3 has, likewise.
If the sheet is filtered, that too may influence what's copied. If there's an Autofilter in place the code might be simpler.
The best thing to do in the first instance is some detective work. Select that Z2 cell on the sheet manually, then on the keyboard, press the End key, then the down-arrow key. The cell that is now selected is the bottom right of the range that would be copied.

Just a guess, since I don't know a sausage about what tables/data there are on the sheet to be copied from, you might try
.Range("A2", .cells(.rows.count,"Z").End(xlup)
instead of the
.Range("Z2").End(xlDown)
part.
But even this can have its gotcha moments - if there's nothing at all in column Z, or just a header in row 1.
You can base the vertical extent of what you want to copy on another column which isguaranteed to have data in every cell. Again just altering the .Range("Z2").End(xlDown) bit:
.Range("A2:Z" & .Range("A2").End(xlDown).row)
or:
.Range("A2:Z" & .cells(.rows.count,"A").End(xlup).row)

Give us a copy of the sheet you're copying from (just one worksheet in a workbook) to avoid our misguided speculation of what might be on that sheet.

[zenjah]

Hi p45cal,

Thank you very much for your reply.

The A column in all 3 templates have non-blank values which is why I couldn't figure out why it was working for 2 of them but not the 3rd

However, I've adopted your suggestion: .Range("A2:Z" & .Range("A2").End(xlDown).Row).Copy
and it's working perfectly now. And it gives me the flexibility of not having to rely on column A to be fully filled for this to work so this is great

Thanks very much for your help

[p45cal]

However, I've adopted your suggestion: .Range("A2:Z" & .Range("A2").End(xlDown).Row).Copy
and it's working perfectly now. And it gives me the flexibility of not having to rely on column A to be fully filled for this to work

…which is exactly what that line does NOT do.

[zenjah]

yes I know - I do want to rely on column A for this purpose. I meant I can change it to say Range("x2") to rely on another column if I need to use it for something else. Thanks again

[p45cal]

OK. but whichever column you choose it has to be fully filled to work reliably.

[zenjah]

yes I am aware of that, thanks