Excel Macro – Loop Command

Hi

I want my front sheet in excel to be a summary sheer for all the other worksheets in the same book.

for example, Cell A1 in summary sheet will be the number in sheet PLOTX001 (PLOTX001!F4*1000).

Similarly, Cell A2 in summary sheet will be the number in sheet PLOTX002 (PLOTX002!F4*1000).

I want to do this for 100 plot sheets and can’t find a way of doing it.

I tried recording a macro (below) but it needs to have a loop or repeat command so that it contiunues until there are no more plot files.

Selection.AutoFill Destination:=Range(“B3:B4”), Type:=xlFillDefault
Range(“B3:B4”).Select
Range(“B4”).Select
ActiveCell.FormulaR1C1 = “=(PLOTX003!RC[4]*1000)”
Range(“B4”).Select
Selection.AutoFill Destination:=Range(“B4:B5”), Type:=xlFillDefault
Range(“B4:B5”).Select
Range(“B5”).Select
ActiveCell.FormulaR1C1 = “=(PLOTX004!R[-1]C[4]*1000)”
Range(“B5”).Select
Selection.AutoFill Destination:=Range(“B5:B6”), Type:=xlFillDefault
Range(“B5:B6”).Select
Range(“B6”).Select
ActiveCell.FormulaR1C1 = “=(PLOTX005!R[-2]C[4]*1000)”
Range(“B7”).Select

As you can see I am struggling. Please help…

3 thoughts on “Excel Macro – Loop Command

  1. Excel Geek

    Hello there

    Try this code below. Using the Workbook_Open method will mean the macro will run everytime you open the workbook to ensure you have accurate figures in your summary sheet. The code needs to be placed in the This Worbook section of the VBA Project.

    The code loops through from 1 to 100. I have assumed that for example your naming convention is as follows… PLOTX001 for one, PLOTX010 for ten and PLOTX100 for the 100th worksheet? If not then let me know.

    Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    For X = 1 To 10 Step 1
    Sheets("Summary").Select
    If X < 10 Then Cells(X, 1) = Sheets("PLOTX00" & X).Cells(4, 6) * 1000
    If X >= 10 And X < = 99 Then Cells(X, 1) = Sheets("PLOTX0" & X) .Cells(4, 6) * 1000
    If X > 99 Then Cells(X, 1) = Sheets("PLOTX" & X).Cells(4, 6) * 1000

    Next X

    Application.ScreenUpdating = True

    End Sub

    Hope this helps

    Thanks for writing!

  2. Zaka

    Hi

    The naming convention is correct. The macro keeps giving me an error. I haven’t been able to run it. Please can I run this macro after opening the workbook. This will be easier as I can create a button to do that for me.

    Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    For X = 1 To 10 Step 1
    Sheets(“Summary”).Select
    If X = 10 And X 99 Then Cells(X, 1) = Sheets(“PLOTX” & X).Cells(4, 6) * 1000

    Next X

    Application.ScreenUpdating = True

    End Sub

    Thanks for the quick reply.

  3. Andrew

    No problem

    Simply put the macro into a module as opposed to the This Workbook section.

    Then you need to rename the macro from Private Sub Workbook_Open() to Sub Summary(). Or almost any name you want.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *