Help with Macro in selecting sheets.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ann - 30 Mar 2007 11:02 GMT

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.

Signature

Thank U and Regards

Ann

Reply to this Message

Bob Phillips - 30 Mar 2007 11:22 GMT

>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
[quoted text clipped - 16 lines]
> 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.
>
[quoted text clipped - 5 lines]
>
> 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

Reply to this Message

Ann - 30 Mar 2007 12:18 GMT

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.

Signature

Thank U and Regards

Ann

> >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
[quoted text clipped - 40 lines]
> sSheets = Left(sSheets, Len(sSheets) - 1)
> Worksheets(Split(sSheets, ",")).Select

Reply to this Message

Bob Phillips - 30 Mar 2007 13:27 GMT

Worksheets("Total").Range("Q2").FormulaR1C1 = _
"=SUM('" & Worksheets(1).Name & ":" & _
Worksheets(Worksheets.Count - 1).Name & "'!RC)"

Signature

HTH

Bob

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

> Bob,
>
[quoted text clipped - 69 lines]
>> sSheets = Left(sSheets, Len(sSheets) - 1)
>> Worksheets(Split(sSheets, ",")).Select

Reply to this Message

Ann - 30 Mar 2007 13:56 GMT

Bob,

What can i say except THANK YOU VERY MUCH.

I am really gratefull for your kind assistance.

Signature

Thank U and Regards

Ann

> Worksheets("Total").Range("Q2").FormulaR1C1 = _
> "=SUM('" & Worksheets(1).Name & ":" & _
[quoted text clipped - 73 lines]
> >> sSheets = Left(sSheets, Len(sSheets) - 1)
> >> Worksheets(Split(sSheets, ",")).Select

Reply to this Message