Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Sheet names/numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Sheet names/numbers
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Sheet names/numbers
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Sheet names/numbers
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page Numbers/Names listed on a Summary sheet? | Excel Discussion (Misc queries) | |||
Sheet Referencing - autofilling sheet names | Excel Worksheet Functions | |||
Displaying information (contained in defined names) on a summary sheet, in different row numbers? | Excel Discussion (Misc queries) | |||
Referencing Sheet Names | New Users to Excel | |||
Referencing Sheet Names In Formula | Excel Programming |