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