Help with Macro in selecting sheets.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?

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.
 
Ann said:
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?
Click to expand...


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.
Click to expand...


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
 
Bob,

Thank U very much for you kind assistance, both your solutions worked.

I have one more question, the last part of the macro does the following:-

In sheet TOTAL (which is the last sheet), cells Q2 to V2, insert a formula
to sum cells Q2 to V2 from the first sheet to the last sheet -1 (Total is the
last sheet)

The macro recorder records as :-

Sheets("Total").Select
Range("Q2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(‘sheet1:sheet69’!RC)"
Range("Q2").Select


Then the last 4 lines for r2, s2, t2, u2 and v2.

How can the above be changed so that sheet names sheet1 and sheet69 are not
hard coded?

Your kind assistance will be much appreciated.
 
Worksheets("Total").Range("Q2").FormulaR1C1 = _
"=SUM('" & Worksheets(1).Name & ":" & _
Worksheets(Worksheets.Count - 1).Name & "'!RC)"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob,

What can i say except THANK YOU VERY MUCH.

I am really gratefull for your kind assistance.
 
Back
Top