+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    carl
    Guest

    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..


  2. #2
    Gary L Brown
    Guest

    RE: 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
    gary_brown@ge_NOSPAM.com
    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..
    >


  3. #3
    Otto Moehrbach
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >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..
    >




  4. #4
    Gary L Brown
    Guest

    RE: 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
    gary_brown@ge_NOSPAM.com
    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
    > gary_brown@ge_NOSPAM.com
    > 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..
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1