R
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
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