Help creating VBA Maco for Excel 2010 for auto-generate large spreadsheet

Posted on 2012-09-19
Last Modified: 2012-09-20
I'm in a bind and could use a hand.  I'm not as familiar with VBA as I need to be, and I'm trying to learn on the fly.  I have a daunting work task that I need to automate ASAP.  I may have some follow up questions because I'm trying to simplify this as much as possible to understand the basic syntax.  

Sheet1 is blank
Sheet2 contains a list of 50 states in column A, and 8 stores in column B.

I need to create a macro that will perform a Do While Loop (or maybe an array?) that will insert the following onto Sheet1:

State1     Store1
State1     Store2
State1     Store3
State1     Store4
State1     Store5
State1     Store6
State1     Store7
State1     Store8

State2     Store1
State2     Store2
State50 Store8

We may need to add to the states column in the future (e.g. add Washington D.C. or Puerto Rico) so I need to have room to expand that (not for this case, but down the road it will be a button for the user to click and generate a new sheet with the updated information).

I think this will require two Do While Loops: one to go through all the states (column A) and one for the stores (Column B).  

Time is critical so I'm offering up 500 pts to the best answer.  

Question by:richecker
    LVL 39

    Accepted Solution

    Try the attached code

    Dim lLastRow1 As Long, lLastRow2 As Long
    Dim lLoop As Long, lRowDest As Long
    'turn off updates to speed up code execution
    With application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    lLastRow1 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    lLastRow2 = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
    lRowDest = 1
    For lLoop = 1 To lLastRow1
        Sheets("Sheet1").Cells(lRowDest, 1).Resize(lLastRow2).Value = Sheets("Sheet2").Cells(lLoop, 1).Value
        Sheets("Sheet2").Cells(1, 2).Resize(lLastRow2).Copy Sheets("Sheet1").Cells(lRowDest, 2)
        lRowDest = lRowDest + lLastRow2
    With application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    End Sub

    Open in new window


    Author Closing Comment

    Much appreciated Nutsch!  This worked just as I needed it to.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article will show you how to use shortcut menus in the Access run-time environment.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now