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

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" <(E-Mail Removed)>
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

Hello Jim Cone,

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

Thank you for your help,
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

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" <(E-Mail Removed)>
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