| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
| Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
| Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Hybrid Mode
