Referencing Sheet names/numbers

[Ron]

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 elsewhere:

Set Sheet1 = Sheets("Sheet1")

This can be confusing

 

[Dave Peterson]

Is Sheet1 is the codename for the worksheet?

When you're in the VBE looking at the project explorer for that project, you can
see:
Sheet1 (SomeSheetNameHere)
under the Microsoft Excel Objects branch

Then name in ()'s is the name of the sheet. It's what you see on the worksheet
tab in excel.

The Sheet1 in front of the ()'s is called the codename.

If the answer is yes, then your program already knows what Sheet1 is and what it
belongs to:

Instead of using this:
ThisWorkbook.Sheet1.Cells(...)
just use:
Sheet1.Cells(...)

 

[Ron]

Hi Dave,

Yes, Sheet1 is the codename and not the sheet name itself.

As the code will open a bundle of other workbooks in turn, and each
workbook will have focus, ie be the ActiveSheet I thought just using the
line Sheet1.Cells(....) would have the code plonk the copied range back
into Sheet1 of the active book.

As the code is residing in a workbook that won't be the active book
during execution I assumed I would have to actually reference the non
active book. Which seems to be the case when I use the line..

reg2.Copy (ThisWorkbook.Sheets(1).Cells(etc etc etc)


I'm still puzzled as to why I can't use the destinations codename, but
can happily get away with using the sheets list number.

Thanks,

Ronnie

 

[Ron]

I was under the impression that Sheet1 Sheet2 etc are the codenames for
worksheets and are more or less set in stone.

I've used the codenames in countless bits of code before and not come
accross this type of problem.

An example would be....[as seen in the VBE]

Sheet1(HoursWorked)
Sheet2(StaffDetails)
Sheet3(JobDetails)

If I wanted to copy a range from "HoursWorked" to "JobDetails" I could
use

Sheet1.Range("A1:A5").Copy (Sheet3.Cells(1, 1))

This works fine without having to declare Sheet3 or Sheet1 as a variable
as Sheet3 is the codename of the sheet called "JobDetails" and no
matter how many times you rename the "JobDetails" sheet it's codename
remains Sheet3

Still puzzled. But thanks for the input.


Ronnie







=?Utf-8?B?R2FyeScncyBTdHVkZW50?=

 

[Dave Peterson]

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"

=====
In fact, using the codename of a worksheet in another workbook requires some
different coding.

I use something like this:

dim wks as worksheet
dim FoundIt as boolean

foundit = false
for each wks in workbooks("someotherworkbook.xls").worksheets
if lcase(wks.codename) = lcase("sheet1") then
foundit = true
exit for
end if
next wks

if foundit = false then
'no sheet with that code name was found
else
msgbox wks.range("A1").address(external:=true)
end if

=====

There are other ways, but some of them depend on the user's security settings.
And since I don't know what the user chose, that code could fail. I just loop
to find the correct worksheet.

 

[Ron]

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]

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]

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