Help with Macro in selecting sheets.
 

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.

--
Thank U and Regards

Ann

Help with Macro in selecting sheets.
 

"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

Help with Macro in selecting sheets.
 

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.

--
Thank U and Regards

Ann



"Bob Phillips" wrote:


"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

Help with Macro in selecting sheets.
 

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)

"Ann" wrote in message
...
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.

--
Thank U and Regards

Ann



"Bob Phillips" wrote:


"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

Help with Macro in selecting sheets.
 

Bob,

What can i say except THANK YOU VERY MUCH.

I am really gratefull for your kind assistance.

--
Thank U and Regards

Ann



"Bob Phillips" wrote:

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)

"Ann" wrote in message
...
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.

--
Thank U and Regards

Ann



"Bob Phillips" wrote:


"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