PDA

View Full Version : Solved: using name in VBA propertys vs Sheet name



mduff
07-22-2010, 09:21 PM
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


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

geekgirlau
07-22-2010, 09:50 PM
If you have the VBE Properties window displayed, you can give the sheet a name. Then you can use the following type of reference:


shData.Range("j5").Copy

mduff
07-22-2010, 09:56 PM
If you have the VBE Properties window displayed, you can give the sheet a name. Then you can use the following type of reference:


shData.Range("j5").Copy



So would shData be the name in the VBE Properties?

geekgirlau
07-22-2010, 10:07 PM
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
07-23-2010, 07:23 AM
thanks I got it to work :)