Array help

[Rick S.]

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

 

[paul.robinson]

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

 

[Dave Peterson]

First thing I'd change is this line:

ReDim Preserve ShtArray(N)
to:
ReDim Preserve ShtArray(1 To N)

 

[Jim Cone]

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

 

[Rick S.]

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

 

[Rick S.]

Dave
Thanks, that was the answer. I simply need to read more on Arrays.
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007