[Ron[_32_]]

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

[Gary''s Student]

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

[Dave Peterson]

Glad you got it working.

I'm not sure if this affects your current project, but you can change the
codename "(Name)" (in the properties window inside the VBE) to something more
meaningful.

Instead of using Sheet1, Sheet2, Sheet3, ...

You could use:
Prices, Accounts, Discounts

And your code would be more self-documenting.

Ron wrote:

Thanks Dave,

That fact that the workbook always knows it's own Sheet1 etc is what was
escaping me.

I was wrongly assuming that the code, no matter where it was residing,
would see the active workbook Sheet1 and not Sheet1 where it's residing.

I know it's sometimes difficult, for me anyway, not being a vba expert to
actualy explain my problem, but you nailed it there mate, thanks a lot.

Ronnie

Dave Peterson wrote in
:

Nope. The codename Sheet1 will always refer to the worksheet in the
workbook with the code.

And since excel/vba knows what it belongs to, you don't "requalify" it
with the ThisWorkbook.

Just like:

Dim rng as range
set rng =
workbooks("Somewkbk.xls").worksheets("somesheet"). range("a1")

You don't use:
workbooks("Somewkbk.xls").worksheets("somesheet"). rng.value = "hi"

You just use:
rng.value = "hi"

=====

--

Dave Peterson

[Ron[_32_]]

Well blow me. I thought the codenames were set in stone. I thought only
the sheet name could be changed.

Thanks Dave that will certainly be useful to me.

Ronnie



Dave Peterson wrote in
:

Glad you got it working.

I'm not sure if this affects your current project, but you can change
the codename "(Name)" (in the properties window inside the VBE) to
something more meaningful.

Instead of using Sheet1, Sheet2, Sheet3, ...

You could use:
Prices, Accounts, Discounts

And your code would be more self-documenting.