Solved: using name in VBA propertys vs Sheet name

[mduff]

Hi

I have this code that Will get Range J5 for the first sheet in the WB the issue I have is people are changing the order of the sheets WB and the names of the tabs (although the data I need will always be in J5 on the sheet)

Is there any way to change this to look at the sheet named (Sheet1) in the VBA propertys (that will not change) and take cell j5 form that

I rememberer some one showed me that once but I can't rememberer how for the life of me


[VBA]Sub WBLoop()

Dim wbk, ThisWorkBook As Workbook
Dim row As Integer

row = 1
Set ThisWorkBook = ActiveWorkbook

For Each wbk In Workbooks
If wbk.Name <> ThisWorkBook.Name Then
wbk.Sheets(1).Range("j5").Copy
ThisWorkBook.Sheets(1).Range("A" & row).Select
'' ThisWorkBook.Sheets(1).PasteSpecial xlPaseValues
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
[/VBA]

[geekgirlau]

If you have the VBE Properties window displayed, you can give the sheet a name. Then you can use the following type of reference:

[VBA]
shData.Range("j5").Copy
[/VBA]

[mduff]

If you have the VBE Properties window displayed, you can give the sheet a name. Then you can use the following type of reference:

[VBA]
shData.Range("j5").Copy
[/VBA]

So would [VBA]shData[/VBA] be the name in the VBE Properties?

[geekgirlau]

Yes. In the Project Explorer, click on the sheet you want to name. Then in the Properties window, change Name to whatever you like.

Generally I would suggest that you name all of your sheets in this way - it makes it a lot easier to reference them, and ensures that they are never impacted by a change in sheet order or tab name.

[mduff]

thanks I got it to work