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,
Steve
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,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
Range("D2:F2").Select
Selection.ClearContents
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(RowNum
Sheets(sheetName).Select
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 just recorded this:
Range("D2:F2").Select
Selection.ClearContents
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(RowNum
Sheets(sheetName).Select
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!
ASKER
Buttersk,
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.
Steve
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.
Steve
ASKER
Couldn't ask for more!!! buttersk must dream VBA code.
ASKER
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)
Steve