Reference 2 separate workbooks!

[jfcby]

Version: Excel 2000 & 2003
* using only one instance

I'm stuck and have no other ideas, I've search the newsgroup and
researched my macros and I can't find my problem with the following
macro.

Workbooks("Equip_List_FF.xls").Worksheets: has about 306 worksheets
visible and hidden. I need to get data from each worksheet and copy it
to Workbooks("FF_Zone5_Bldgs.xls").Worksheets("WATER TREATMENT -
(ALPH)").

But, when I run this macro it gives me the error "Object required" and
I can't figure out why. Could someone point me in the right direction
to fix this error, than you for your help.

Sub WorkSheetsBldgNumberNameCellB()
'Worksheets Building Name & Number in cell
'------
Dim wbkA As Workbook, wbkB As Workbook
Dim wksA As Worksheet, wksB As Worksheet
Application.ScreenUpdating = False
Set wbkA = Workbooks("Equip_List_FF.xls")
Set wbkB = Workbooks("FF_Zone5_Bldgs.xls")
Set wksB = wbkB.Worksheets("WATER TREATMENT - (ALPH)")
'------
For Each wksA In wbkA.Worksheets
If wksA.Visible = xlSheetVisible Then
'------>>
wksB.Worksheets("WATER TREATMENT -
(ALPH)").Range("B65535").End(xlUp).Offset(1, 0).Address = _
Right(wksA.Range("C2"), 4).Value '1 below last row of data skip
blanks
'------>>
wksB.Range("B65535").End(xlUp).Offset(, 1) =
Left(wksA.Range("C1").Value, _
Len(wksA.Range("C1").Value) - 16)
End If
Next
Application.ScreenUpdating = True
End Sub

Thank you for your help,
jfcby

 

[Don]

Version: Excel 2000 & 2003
* using only one instance

I'm stuck and have no other ideas, I've search the newsgroup and
researched my macros and I can't find my problem with the following
macro.

Workbooks("Equip_List_FF.xls").Worksheets: has about 306 worksheets
visible and hidden. I need to get data from each worksheet and copy it
to Workbooks("FF_Zone5_Bldgs.xls").Worksheets("WATER TREATMENT -
(ALPH)").

But, when I run this macro it gives me the error "Object required" and
I can't figure out why. Could someone point me in the right direction
to fix this error, than you for your help.

Sub WorkSheetsBldgNumberNameCellB()
'Worksheets Building Name & Number in cell
'------
Dim wbkA As Workbook, wbkB As Workbook
Dim wksA As Worksheet, wksB As Worksheet
Application.ScreenUpdating = False
Set wbkA = Workbooks("Equip_List_FF.xls")
Set wbkB = Workbooks("FF_Zone5_Bldgs.xls")
Set wksB = wbkB.Worksheets("WATER TREATMENT - (ALPH)")
'------
For Each wksA In wbkA.Worksheets
If wksA.Visible = xlSheetVisible Then
'------>>
wksB.Worksheets("WATER TREATMENT -
(ALPH)").Range("B65535").End(xlUp).Offset(1, 0).Address = _
Right(wksA.Range("C2"), 4).Value '1 below last row of data skip
blanks
'------>>
wksB.Range("B65535").End(xlUp).Offset(, 1) =
Left(wksA.Range("C1").Value, _
Len(wksA.Range("C1").Value) - 16)
End If
Next
Application.ScreenUpdating = True
End Sub

Thank you for your help,
jfcby

Looks like

wksB.Worksheets("WATER TREATMENT -
(ALPH)").Range("B65535").End(xlUp).Offset(1, 0).Address = _
Right(wksA.Range("C2"), 4).Value '1 below last row of data skip
blanks

Should be

wbkB.Worksheets("WATER TREATMENT -
(ALPH)").Range("B65535").End(xlUp).Offset(1, 0).Address = _
Right(wksA.Range("C2"), 4).Value '1 below last row of data skip
blanks

 

[James F Cooper]

Hello,

Thank you Don for your help!

What a challenge, but, this is the working VBA Macro Code:

'>>-- MACRO --
Sub WorkSheetsBldgNumberNameCellB()
'Copy data from workbook 1 visible worksheets to workbook 2 worksheet(1
worksheet only)
Dim wbkA As Workbook
Dim wbkB As Workbook
Dim wksA As Worksheet
Dim wksB As Worksheet
Application.ScreenUpdating = False
Set wbkA = Workbooks("Equip_List_FF.xls")
Set wbkB = Workbooks("FF_Zone5_Bldgs.xls")
Set wksB = wbkB.Worksheets("WATER TREATMENT - ALPH")
'wbkA(workbooks) wksA(worksheets)
For Each wksA In wbkA.Worksheets
'Visible worksheets only
If wksA.Visible = xlSheetVisible Then
'Copy data from wksA(worksheet) Range to wksB(worksheet) 1 below last
row of data skip blanks
wksB.Range("B65535").End(xlUp).Offset(1, 0) =
Right(wksA.Range("C2").Value, 4)
'Copy data from wksA(worksheet) Range to wks(worksheet) offset 1 to the
right of cell
wksB.Range("B65535").End(xlUp).Offset(0, 1) =
Left(wksA.Range("C1").Value, _
Len(wksA.Range("C1").Value) - 16)
End If
Next
Application.ScreenUpdating = True
End Sub
'<<-- MACRO --

Thank you for your help,


jfcby