VBA excel: creating a list from sheet 1 onto sheet 2

Posted on 2011-10-24
Last Modified: 2012-05-12
The attached file contains account information on customers. I need to write a sub that creates a list on sheet 2 of all the customers who still owe more than $1000. It should first clear the contents of any previous list on sheet 2 and must show customer IDs and the amounts owed and should work even if the amount of data changes.

I am learning excel and would prefer if the solution is simple. Thanks!!
Question by:buutercup0122
    LVL 41

    Accepted Solution

    app clears the output sheet (sheet2), then goes through all of column A on Sheet 1, looking at amount purchased less paid.  If that amount >= $1,000, an entry is made on the output sheet, with customer ID and amount owed

    Here's the code:
    Sub genList()
    Dim wkb As Workbook
    Dim srcSht As Worksheet
    Dim outSht As Worksheet
    Dim rng As Range
    Dim outCursor As Range
    Dim lastRow As Long
    Dim checkOwed As Double
        Set wkb = ThisWorkbook
        Set srcSht = wkb.Sheets("Sheet1")
        Set outSht = wkb.Sheets("Sheet2")
        'first, clear Sheet2
        lastRow = outSht.Range("A" & outSht.Rows.Count).End(xlUp).Row
        If lastRow >= 4 Then
            outSht.Range("A4", "A" & lastRow).EntireRow.ClearContents
        End If
        Set outCursor = outSht.Range("A4")
        For Each rng In srcSht.Range("A4", srcSht.Range("A" & srcSht.Rows.Count).End(xlUp))
            checkOwed = rng.Offset(0, 1).Value - rng.Offset(0, 2).Value 'amount owed - amount paid
            If checkOwed >= 1000 Then 'copy to output sheet
                outCursor.Value = rng.Value 'customer_ID
                outCursor.Offset(0, 1).Value = checkOwed 'and amount owed
                Set outCursor = outCursor.Offset(1, 0) 'proceed to next row for next customer
            End If
        Next rng
        MsgBox "Process Complete, Hit Enter to see outputs", vbOKOnly
    End Sub

    Open in new window

    See attached workbook.

    PS - this could have been done with a pivot table, but I'm assuming you're doing this to get some VBA exposure as well, so hope you found this helpful!

    LVL 41

    Expert Comment

    FYi - and for fun.  Here's the same workbook, where I added an additional Column to your source data, called Amount Owed.

    I then Created a Pivot table on those 3 columns, and then created a filter from the Row values Customer ID, on Amount Owed, >= 1000 to generate the same list, using a pivot table.



    Author Comment

    Thank you so much, Dave. I really appreciate it. I especially liked that you included your explanations. Also, thank you for the extra explanation of a pivot table! I am truly amazed with your knowledge.
    Thanks again,

    Author Closing Comment

    Perfect. Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    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!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now