Copy Worksheet, Excel 2000 & 2003

[jfcby]

Hello,

The following macro code gives the error: "Subscript out of range" on
this line: "wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)".

I'm trying to copy certain worksheets form one workbook to another
workbook.

Why am I getting this error and how can the following macro be
modified to copy the worksheets?

Sub CopyWorkSheets_()
Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_Zone5_Bldgs.xls")
For Each wsA In wbA.Worksheets
For Each cell In wbB.Worksheets("Index").Range("E4:E27")
If wsA.Visible = xlSheetHidden Then GoTo nws
'MsgBox "Worksheet Name = " & wsA.Name & " " & "Value = " &
Right(wsA.Range("C2").Value, 4) _
& " " & "cell = " & cell
If cell.Text = Right(wsA.Range("C2").Text, 4) Then
wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)
GoTo nws
End If
Next cell
nws:
Next wsA
End Sub

Than you for your help,
jfcby

 

[Jim Cone]

1. Are both workbooks open?
2. Since the subject of your message references two different versions
of Excel - are both workbooks open in the same instance of Excel?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"jfcby" <[email protected]>
wrote in message
Hello,
The following macro code gives the error: "Subscript out of range" on
this line: "wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)".
I'm trying to copy certain worksheets form one workbook to another
workbook.
Why am I getting this error and how can the following macro be
modified to copy the worksheets?

Sub CopyWorkSheets_()
Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_Zone5_Bldgs.xls")
For Each wsA In wbA.Worksheets
For Each cell In wbB.Worksheets("Index").Range("E4:E27")
If wsA.Visible = xlSheetHidden Then GoTo nws
'MsgBox "Worksheet Name = " & wsA.Name & " " & "Value = " &
Right(wsA.Range("C2").Value, 4) _
& " " & "cell = " & cell
If cell.Text = Right(wsA.Range("C2").Text, 4) Then
wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)
GoTo nws
End If
Next cell
nws:
Next wsA
End Sub

Than you for your help,
jfcby

 

[jfcby]

Hello Jim Cone,

Yes, both worksheets are open in the same version of Excel.

Thank you for your help,
jfcby

 

[jfcby]

Hello,

Thank you for your help!

This is the working macro code:

Sub CopyWorkSheets_()
Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_Zone5_Bldgs.xls")
For Each wsA In wbA.Worksheets
For Each cell In wbB.Worksheets("Index").Range("E4:E27")
If wsA.Visible = xlSheetHidden Then GoTo nws
'MsgBox "Worksheet Name = " & wsA.Name & " " & "Value = " &
Right(wsA.Range _("C2").Value, 4) & " " & "cell = " & cell
If cell.Text = Right(wsA.Range("C2").Text, 4) Then
wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)
GoTo nws
End If
Next cell
nws:
Next wsA
End Sub

Thank you,
jfcby

 

[Jim Cone]

I don't know if you mean the code works now or not?
I don't see any difference in the two versions.
In any case here is a modified version (untested) that you can try.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'---
Sub CopyWorkSheets_()
Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Dim cell As Range
Dim strT As String

Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_Zone5_Bldgs.xls")

For Each wsA In wbA.Worksheets
If wsA.Visible = xlSheetHidden Then
strT = Right$(wsA.Range("C2").Text, 4)
For Each cell In wbB.Worksheets("Index").Range("E4:E27")
If cell.Text = strT Then
wsA.Visible = xlSheetVisible
wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)
wsA.Visible = xlSheetHidden
Exit For
End If
Next cell
End If
Next wsA
End Sub
'---


"jfcby" <[email protected]>
wrote in message
Hello,
Thank you for your help!
This is the working macro code:

Sub CopyWorkSheets_()
Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_Zone5_Bldgs.xls")
For Each wsA In wbA.Worksheets
For Each cell In wbB.Worksheets("Index").Range("E4:E27")
If wsA.Visible = xlSheetHidden Then GoTo nws
'MsgBox "Worksheet Name = " & wsA.Name & " " & "Value = " &
Right(wsA.Range _("C2").Value, 4) & " " & "cell = " & cell
If cell.Text = Right(wsA.Range("C2").Text, 4) Then
wsA.Copy after:=wbB.Sheets(wbB.Sheets.Count)
GoTo nws
End If
Next cell
nws:
Next wsA
End Sub

Thank you,
jfcby