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…

2,690 thoughts on “Excel Macro – Loop Command

  1. dinner dates

    I think other website proprietors should take this site as an model, very clean and great user genial style and design, as well as the content. You are an expert in this topic!

  2. important source

    I just want to mention I am all new to blogging and actually savored you’re page. Almost certainly I’m going to bookmark your blog post . You actually come with outstanding stories. Cheers for sharing your blog.

  3. click to investigate

    I simply want to tell you that I am very new to blogging and site-building and really loved your blog site. Most likely I’m going to bookmark your site . You surely have remarkable articles. Cheers for revealing your website page.

  4. tinyurl.com

    Magnificent beat ! I would like to apprentice while you amend your site, how can i
    subscribe for a blog site? The account aided me a acceptable deal.

    I had been a little bit acquainted of this your broadcast offered bright clear
    concept

  5. Tattoo

    That is a great tip especially to those fresh to the blogosphere. Simple but very accurate information Thanks for sharing this one. A must read post!

  6. Tattoos

    Whoa! This blog looks just like my old one! It as on a entirely different topic but it has pretty much the same layout and design. Outstanding choice of colors!

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

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

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

Comments are closed.