VBA Code To have a macro repeat on all sheets in a workbook
 

I am using this code:

Range("E9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlToRight).Select
Range("J9").Select
ActiveSheet.Paste
Range("J10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-5]*RC2"
Range("J10").Select
ActiveWindow.SmallScroll Down:=-6
Selection.NumberFormat = "0"
Selection.Copy
Range("I10").Select
Selection.End(xlDown).Select
Range("J495:O495").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J494").Select
Selection.End(xlUp).Select
Range("J8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[1000]C)/SUM(R10C2:R1000C2)"
Range("J8").Select
Selection.NumberFormat = "0.00%"
Selection.Copy
Range("K8:N8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J9").Select
Selection.End(xlToLeft).Select
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=SUM(R[2]C:R[1000]C)/SUM(R[2]C[-1]:R[1000]C[-1])"
Range("C8").Select
Selection.NumberFormat = "0.00%"
End Sub

Is it possible to add code to this that would allow the macro to be started
once and then repeat for each worksheet in my workbook ?

Thank you in advance..

VBA Code To have a macro repeat on all sheets in a workbook
 

Dim wksht As Worksheet
For Each wksht In Application.Worksheets
YOUR CODE HERE
Next wksht

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"carl" wrote:

I am using this code:

Range("E9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlToRight).Select
Range("J9").Select
ActiveSheet.Paste
Range("J10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-5]*RC2"
Range("J10").Select
ActiveWindow.SmallScroll Down:=-6
Selection.NumberFormat = "0"
Selection.Copy
Range("I10").Select
Selection.End(xlDown).Select
Range("J495:O495").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J494").Select
Selection.End(xlUp).Select
Range("J8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[1000]C)/SUM(R10C2:R1000C2)"
Range("J8").Select
Selection.NumberFormat = "0.00%"
Selection.Copy
Range("K8:N8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J9").Select
Selection.End(xlToLeft).Select
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=SUM(R[2]C:R[1000]C)/SUM(R[2]C[-1]:R[1000]C[-1])"
Range("C8").Select
Selection.NumberFormat = "0.00%"
End Sub

Is it possible to add code to this that would allow the macro to be started
once and then repeat for each worksheet in my workbook ?

Thank you in advance..

VBA Code To have a macro repeat on all sheets in a workbook
 

One way: HTH Otto

Sub AllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
'Your code
Next
End Sub
"carl" wrote in message
...
I am using this code:

Range("E9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlToRight).Select
Range("J9").Select
ActiveSheet.Paste
Range("J10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-5]*RC2"
Range("J10").Select
ActiveWindow.SmallScroll Down:=-6
Selection.NumberFormat = "0"
Selection.Copy
Range("I10").Select
Selection.End(xlDown).Select
Range("J495:O495").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J494").Select
Selection.End(xlUp).Select
Range("J8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[1000]C)/SUM(R10C2:R1000C2)"
Range("J8").Select
Selection.NumberFormat = "0.00%"
Selection.Copy
Range("K8:N8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J9").Select
Selection.End(xlToLeft).Select
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=SUM(R[2]C:R[1000]C)/SUM(R[2]C[-1]:R[1000]C[-1])"
Range("C8").Select
Selection.NumberFormat = "0.00%"
End Sub

Is it possible to add code to this that would allow the macro to be
started
once and then repeat for each worksheet in my workbook ?

Thank you in advance..

VBA Code To have a macro repeat on all sheets in a workbook
 

Otto's right. I forgot that you're copying and pasting. So to keep your
code prestine you HAVE TO go to the worksheet prior to running your code.

Sincerely,
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Gary L Brown" wrote:

Dim wksht As Worksheet
For Each wksht In Application.Worksheets
YOUR CODE HERE
Next wksht

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"carl" wrote:

I am using this code:

Range("E9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlToRight).Select
Range("J9").Select
ActiveSheet.Paste
Range("J10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-5]*RC2"
Range("J10").Select
ActiveWindow.SmallScroll Down:=-6
Selection.NumberFormat = "0"
Selection.Copy
Range("I10").Select
Selection.End(xlDown).Select
Range("J495:O495").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J494").Select
Selection.End(xlUp).Select
Range("J8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[1000]C)/SUM(R10C2:R1000C2)"
Range("J8").Select
Selection.NumberFormat = "0.00%"
Selection.Copy
Range("K8:N8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J9").Select
Selection.End(xlToLeft).Select
Range("C8").Select
ActiveCell.FormulaR1C1 =
"=SUM(R[2]C:R[1000]C)/SUM(R[2]C[-1]:R[1000]C[-1])"
Range("C8").Select
Selection.NumberFormat = "0.00%"
End Sub

Is it possible to add code to this that would allow the macro to be started
once and then repeat for each worksheet in my workbook ?

Thank you in advance..