The difference is that Sheets(1) will refer to a specific sheet. Sheet1, as
you have used it, is a variable that must set set elsewhe

Set Sheet1 = Sheets("Sheet1")

This can be confusing
--
Gary''s Student - gsnu2007h


"Ron" wrote:

Hi Guys,

I have a macro in a workbook that is opening and checking 1700 other
workbooks. If it finds what it wants it copies a small range into the
workbook the macro resides in.

reg1 & reg2 are the two small regions in the temp workbook that is open
and active during the code execution.

This works....

Set reg1 = Range(Cells(1, 13), Cells(Rows.Count, "Q").End(xlUp))
Set reg2 = Range(Cells(1, 18), Cells(Rows.Count, "U").End(xlUp))

reg1.Copy (ThisWorkbook.Sheets(1).Cells(Rows.Count, "A").End(xlUp) _
..Offset(1, 0))

reg2.Copy (ThisWorkbook.Sheets(1).Cells(Rows.Count, "G").End(xlUp) _
..Offset(1, 0))


But this doesn't...

Set reg1 = Range(Cells(1, 13), Cells(Rows.Count, "Q").End(xlUp))
Set reg2 = Range(Cells(1, 18), Cells(Rows.Count, "U").End(xlUp))

reg1.Copy (ThisWorkbook.Sheet1.Cells(Rows.Count, "A").End(xlUp) _
..Offset(1, 0))

reg2.Copy (ThisWorkbook.Sheet1.Cells(Rows.Count, "G").End(xlUp) _
..Offset(1, 0))


The only difference being the way I'm trying to reference the
destination sheet. The first method of referencing the first numbered
sheet is ok and as it's a one off piece of code I can use this. But the
second error puzzles me as I would rather reference the sheet by using
Sheet1 as this wouldn't affect anything should the sheet be moved in the
list or renamed.

I know I haven't technically got a problem but if I don't ask I can't
learn.

Any help guys?

Thanks,

Ronnie