|
Array help
I do not understand why this code works in Excel 2003 and not in 2007?
The code is from a NG. I have five worksheets named: "OP 10 (1), OP 10 (2), OP 10 (3), OP 20 (1) and OP 100(1). The macro should copy all "OP 10" worksheets to the end of worksheet "OP 10 (3)" as "OP 10 (4), OP 10 (5) and OP 10 (6)". Being "Array Challenged", I do not understand the code very well. '====== Sub CopySelectSheets() 'Excel 2003 Dim N As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N)) 'Above: Run-time error "9", subscript out of range. End If End Sub '====== All input is appreciated. -- Regards VBA.Newb.Confused XP Pro Office 2007 |
Array help
Hi
Does this do what you want Sub CopySelectSheets() 'Excel 2003 Dim N As Long, i As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then For i = 1 To N Sheets(ShtArray(i)).Copy After:=Sheets(ShtArray(N)) Next i End If End Sub regards Paul On Feb 26, 7:54*pm, Rick S. wrote: I do not understand why this code works in Excel 2003 and not in 2007? The code is from a NG. I have five worksheets named: "OP 10 (1), OP 10 (2), OP 10 (3), OP 20 (1) and OP 100(1). *The macro should copy all "OP 10" worksheets to the end of worksheet "OP 10 (3)" as "OP 10 (4), OP 10 (5) and OP 10 (6)". Being "Array Challenged", I do not understand the code very well. '====== Sub CopySelectSheets() 'Excel 2003 * Dim N As Long * Dim ShtArray() As Variant * Dim Wks As Worksheet * * For Each Wks In Worksheets * * * If Wks.Name < "ListA" Then * * * * If Wks.Cells(4, "D").Value = "10" Or _ * * * * Wks.Cells(4, "D").Value = "10" & " CONT" Then * * * * * *N = N + 1 * * * * * *ReDim Preserve ShtArray(N) * * * * * *ShtArray(N) = Wks.Name * * * * * *End If * * * * End If * * Next Wks * MsgBox ShtArray(N) 'for testing, shows last sheet in array * * If N 0 Then * * * *Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N)) * * * *'Above: Run-time error "9", subscript out of range. * * End If End Sub '====== All input is appreciated. -- Regards VBA.Newb.Confused XP Pro Office 2007 |
Array help
First thing I'd change is this line:
ReDim Preserve ShtArray(N) to: ReDim Preserve ShtArray(1 To N) Rick S. wrote: I do not understand why this code works in Excel 2003 and not in 2007? The code is from a NG. I have five worksheets named: "OP 10 (1), OP 10 (2), OP 10 (3), OP 20 (1) and OP 100(1). The macro should copy all "OP 10" worksheets to the end of worksheet "OP 10 (3)" as "OP 10 (4), OP 10 (5) and OP 10 (6)". Being "Array Challenged", I do not understand the code very well. '====== Sub CopySelectSheets() 'Excel 2003 Dim N As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N)) 'Above: Run-time error "9", subscript out of range. End If End Sub '====== All input is appreciated. -- Regards VBA.Newb.Confused XP Pro Office 2007 -- Dave Peterson |
Array help
Range("D4") on each sheet must contain "10" or "10CONT" or the sheet doesn't get copied. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Rick S." wrote in message I do not understand why this code works in Excel 2003 and not in 2007? The code is from a NG. I have five worksheets named: "OP 10 (1), OP 10 (2), OP 10 (3), OP 20 (1) and OP 100(1). The macro should copy all "OP 10" worksheets to the end of worksheet "OP 10 (3)" as "OP 10 (4), OP 10 (5) and OP 10 (6)". Being "Array Challenged", I do not understand the code very well. '====== Sub CopySelectSheets() 'Excel 2003 Dim N As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N)) 'Above: Run-time error "9", subscript out of range. End If End Sub '====== All input is appreciated. -- Regards VBA.Newb.Confused XP Pro Office 2007 |
Array help
Paul
Thank you for your input, the code worked but it places worksheets as in 6, 5, 4 instead of 4,5,6. -- Regards VBA.Newb.Confused XP Pro Office 2007 " wrote: Hi Does this do what you want Sub CopySelectSheets() 'Excel 2003 Dim N As Long, i As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then For i = 1 To N Sheets(ShtArray(i)).Copy After:=Sheets(ShtArray(N)) Next i End If End Sub regards Paul On Feb 26, 7:54 pm, Rick S. wrote: I do not understand why this code works in Excel 2003 and not in 2007? The code is from a NG. I have five worksheets named: "OP 10 (1), OP 10 (2), OP 10 (3), OP 20 (1) and OP 100(1). The macro should copy all "OP 10" worksheets to the end of worksheet "OP 10 (3)" as "OP 10 (4), OP 10 (5) and OP 10 (6)". Being "Array Challenged", I do not understand the code very well. '====== Sub CopySelectSheets() 'Excel 2003 Dim N As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N)) 'Above: Run-time error "9", subscript out of range. End If End Sub '====== All input is appreciated. -- Regards VBA.Newb.Confused XP Pro Office 2007 |
Array help
Dave
Thanks, that was the answer. I simply need to read more on Arrays. ;) -- Regards VBA.Newb.Confused XP Pro Office 2007 "Dave Peterson" wrote: First thing I'd change is this line: ReDim Preserve ShtArray(N) to: ReDim Preserve ShtArray(1 To N) Rick S. wrote: I do not understand why this code works in Excel 2003 and not in 2007? The code is from a NG. I have five worksheets named: "OP 10 (1), OP 10 (2), OP 10 (3), OP 20 (1) and OP 100(1). The macro should copy all "OP 10" worksheets to the end of worksheet "OP 10 (3)" as "OP 10 (4), OP 10 (5) and OP 10 (6)". Being "Array Challenged", I do not understand the code very well. '====== Sub CopySelectSheets() 'Excel 2003 Dim N As Long Dim ShtArray() As Variant Dim Wks As Worksheet For Each Wks In Worksheets If Wks.Name < "ListA" Then If Wks.Cells(4, "D").Value = "10" Or _ Wks.Cells(4, "D").Value = "10" & " CONT" Then N = N + 1 ReDim Preserve ShtArray(N) ShtArray(N) = Wks.Name End If End If Next Wks MsgBox ShtArray(N) 'for testing, shows last sheet in array If N 0 Then Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N)) 'Above: Run-time error "9", subscript out of range. End If End Sub '====== All input is appreciated. -- Regards VBA.Newb.Confused XP Pro Office 2007 -- Dave Peterson |
| All times are GMT +1. The time now is 01:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com