Avatar of wrfdchief
Flag for United States of America asked on

Copy different rows to different worksheets in Excel 2010 with VBA

Hi all,

 I consider myself an advanced ameteur in Excel. In other words I know enough to get myself into trouble but not enough to get out. I have worked with both formulas and VBA.

Here is my quandry,

I have a workbook that is like a budget ledger. In this ledger file I have about 60 different categories and a seperate worksheet for each. I have a main page that lists all of those categories like a summary page with the individual worksheets for each category that lists all individual expenses that were charged to that category. So far so good and that is all working well. When we charge something to the budget we fill out a voucher form for each vendor. This form has 10 lines with info like invoice no., date, amount and budget category. Each voucher sheet can contain up to 10 different 'charges' some of the charges may be charged to different budget categories in the same voucher sheet.  For example a voucher from vendor 'ACME' may have one line or charge that goes to 'Category A' in the budget but the next 2 lines may each be charged to 'category B' if that makes sense.

I am making a worksheet with 10 rows to enter data to mimic this 'voucher sheet'. I will input the info. I have a data validation drop down for each row that selects the category (It is also the same as the appropriate worksheet name). Once I fill in the info I want to be able to click a 'submit' button and have it copy each of the up to 10 lines to the first blank line in the appropriate worksheet . Again the worksheet name is selected in coulmn A for each row via data validation. Then I want it to clear the voucher worksheet so it is ready to enter another one.

I know this is kinda a tall order for a newb here but I usually try to figure these things out on my own. I'm just in to deep here.

Thanks in Advance,

Microsoft ExcelVB Script

Avatar of undefined
Last Comment

8/22/2022 - Mon
Ken Butters

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question


The example is almost perfect.  You get the points but I have a couple of questions.  Remember I am very much an ameteur with this.

1) How do I erase or blank out the invoive entry sheet when it is done??

2) Does the VBA code go into a module as yours did, or can it go into the sheet code or workbook code?

3) If in the category sheets there were a couple of extra columns in between the data, how hard would it be to still copy.  For example (I realize I didn't give this info earlier) On my voucher entry page there is one cell at the top for voucher number.  All of the rows entered would have the same voucher number when the data gets copied to the individual pages.  This in turn is one of the extra columns in between the data to be copied.  To best illustrate it use your invoice entry sheet.  But when the data is copied to the category sheet, the columns would be 'Invoice No.' then Voucher No. then date then expense amount.  The voucher no is in between and is obtained from a single cell in the  invoice entry sheet (e.g. D2)

Ken Butters

1) If you looked at the method I used, I used both a copy and a paste.   In each instance before the copy and before the paste, you have to select the target... the same would be true of blanking out cells.

I just recorded this:

When in doubt, and it seems like something you can record with a macro... do it and look at the resulting VBA code.  It is often very helpful, especially when you are first learning.

another way of specifying Range is with the cells command.  That is useful when you know the row/column by number.

so the command Range("D2:F2").Select could also be written as
Range(Cells(2, 4), Cells(2, 6)).Select

Note:the first parameter of cells() is Row, the second is colulmn.

2) Code like that I usually put in a module.  The sheet I would reserve for Events that the sheet triggers, and likewise for the Workbook code, Events that the workbook triggers.  In the Visual Basic Editor when you have a sheet or workbook selected, pre-defined events are usually listed in the various drop-downs at the top for those objects.

3) If there are extra columns you just have to do extra copy and paste actions... the same as if you were doing it by hand.

The copy and paste I used is:

    Sheets("Invoice Entry").Range(Cells(RowNumber, INVOICE_NUMBER_COL), Cells(RowNumber, AMOUNT_COL)).Copy
    Cells(myLastCell.Row + 1, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
You would just adjust the above lines to account for the multiple non-contiguous sections that you need to copy and paste.

Hope that helps!


I can't tell you how much I appreciate your assistance.  You gave me very streamlined clear code example and the same with the explainations.  You don't know how much you've helped a newb out!!!!!

P.S. Please stay tuned to this thread.  I may have more issues...lol.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Couldn't ask for more!!!  buttersk must dream VBA code.