Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet, Excel 2000 & 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet, Excel 2000 & 2003
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet, Excel 2000 & 2003
Hello Jim Cone,
Yes, both worksheets are open in the same version of Excel. Thank you for your help, jfcby |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet, Excel 2000 & 2003
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet, Excel 2000 & 2003
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows in locked worksheet - from Excel 2003 to 2000 | Excel Discussion (Misc queries) | |||
Add data to calendar from worksheet, Excel 2000-2003 | Excel Programming | |||
worksheet in excel 2000 different than 2003 | Excel Discussion (Misc queries) | |||
In Excel 2000, Cannot edit cells in worksheet created in 2003? | Excel Discussion (Misc queries) | |||
worksheet protection excel 2000 vrs 2003 | Excel Programming |