"Ann" wrote in message
...
I have recorded a macro and am experiencing problems because during the
recording of the macro sheet names have been hard coded. As time has
progressed, extra data has been added and extra sheets have been added and
therefore when I run it I get the message "Run time Error '9', Subscript
out
of range". When I select debug it points to the line that starts
Sheets(Array("sheet 1", "sheet 2",.."

I therefore have 2 problems:-

Problem 1.

Sheets("ZZZ ABC").Select
Sheets("ZZZ ABC ").Copy After:=Sheets(67)
Sheets("ZZZ ABC (2)").Select
Sheets("ZZZ ABC (2)").Name = "Total"

How can the above be changed so that rather than selecting sheet called
ZZZ
ABC, the very last sheet is selected, copied and then renamed to Total?


Worksheets(Worksheets.Count).Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "Total"

Problem 2.

How can the array statement be changed so that it selects all sheets
except
TOTAL? rather than as present which looks like:-

Sheets(Array("sheet 1", "sheet 2",.."

Please help, thank you in anticipation.


Dim sSheets As String
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.Name < "Total" Then
sSheets = sSheets & sh.Name & ","
End If
Next sh
sSheets = Left(sSheets, Len(sSheets) - 1)
Worksheets(Split(sSheets, ",")).Select