I currently use 2003, 2007 and 2010 versions of Excel, and do know how to record and run a macro, though I do not write VB. I have an invoice template which I bring in, fill in individual details using data validation/drop down boxes) and print off. I then want to be able to copy, for instance, data in cells c21, c12, e18, and g59 (in that order) to an external workbook in a row format so that at end of each month I have date, name, ref and total in tabular format. I can easily write the macro copying the data over to the other workbook but can't get it to move down one line so that when I copy a subsequent invoice data over, it doesn't overwrite what was previously pasted. I would appreciate any help.
Try this adjusting the names of the sheets/workbooks in the first three lines as appropriate.
The workbook containing the invoice template needs to be active and the external workbook open.Code:Sub CopySpecial() Const SourceSheet = "Invoice template" Const TargetSheet = "Tabular view" Const ExternalWorkbook = "My external workbook.xls" Sheets(SourceSheet).Range("$C$21").Copy Destination:=Workbooks(ExternalWorkbook).Sheets(TargetSheet).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Sheets(SourceSheet).Range("$C$12").Copy Destination:=Workbooks(ExternalWorkbook).Sheets(TargetSheet).Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) Sheets(SourceSheet).Range("$E$18").Copy Destination:=Workbooks(ExternalWorkbook).Sheets(TargetSheet).Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) Sheets(SourceSheet).Range("$G$59").Copy Destination:=Workbooks(ExternalWorkbook).Sheets(TargetSheet).Cells(Rows.Count, 1).End(xlUp).Offset(0, 3) End Sub
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks