Excel - copy data macro Solved/Closed

Question

Hello,

I am currently learning the tricks in excel but have become stuck.
I have created an expenses sheet where employees record various expenses.  I have created a macro which copies the entries into a master expense sheet.  
The expense lines are recorded within a range of cells and there are 2 other cells on the sheet containing employee name and ID.  In the case of multiple lines, when I run the macro, it copies all the expense lines but only puts the name and ID next to the first line.  I would like for this to be copied next to each expense line.  Please help!!

venkat1926 · Answer

depends upon the how the sheet is configured.
upload your file ( with random data for security reasons) to  speedyshare.com and give the webpage from where it can be downloaded.

fazster · Answer

The spreadsheet is here:

http://speedy.sh/7yzC6/sample.xlsm

the macro im using is:

http://speedy.sh/fQxSP/Sub-Test.docx

the data has been copied to:

http://speedy.sh/mu5Rt/assist.xlsx

as you can see the 3 lines copy over but the employee id and name only appear with the first.

venkat1926 · Answer

what is the source file from which you copy 
I assume that  "thisworkbook" that is the workbook in which the macro 
is parked is the source file. the name of this file is not known

destination file is "master expenses.xlsx" in the folder 
"E:\Spreadsheets - Paul\"
is this file same as the file you have sent (assit-1.xlsx")


assume at the beginning there is no data in the workbook "maserexpenses"
so the range(A11:K20) from thisworkbook  is copied from c2 to  k11 in the 
workbook "master expenses"

according to your code from souce book C5 is pasted on A2 in thisworkbook
similarly from source book c6 is pased on A3 of this workbook
because there is not data after A2 down in this workbook
you want A2:B2 is to be copied both in A3:B3 and also A4:B4

make this change and see whether it helps

ThisWorkbook.Sheets("Expenses").Range("C6").Copy
 Workbooks("MasterExpenses.xlsx").Sheets("Expenses").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
With Workbooks("MasterExpenses.xlsx").Sheets("Expenses")
.Range("A2:B2").Copy .Range("a3:A4")
End With
Workbooks("MasterExpenses.xlsx").Save
Workbooks("MasterExpenses.xlsx").Close

fazster · Answer

Thanks for your reply...I haven't managed to implement it yet but wouldn't that always just copy that range and paste it.  I think I might have explained a bit weirdly, sorry I'm new to it!

Basically, the employee enters expense lines into the main spreadsheet in the range A11:K20.  The employee can enter a maximum of ten lines at one time.  What I want the macro to do is to copy all the lines that have been entered be it 1 or 10 and paste them to the master expenses sheet.  Yes originally the master expenses sheet would be empty and data is added to it each time an employee submits expenses.  The macro needs to paste all the expense lines and effectively count the number of lines in that submission and paste the ID and name the same number of times into column A and B of the master expenses sheet (named assist in this thread).  The next time an employee submits an expense the lines are to be copied and pasted again with the ID and name the same number of times against the relevant rows.  

Hopefully, it makes a bit more sense now!

venkat1926 · Answer

this is essential design of t he sheet problem. has to be discussed with some expert 

suggestions

there should be an empty template 
you design this to have employee name and other particulars 
employee enters the data 10 or 15 line
once he finished entering all the data he/she has to click a button and the dta 
will be transferred to main sheet and the exlployee's sheet will be cleared of data for next employee to fill i
something like this you have to design and then macro can be written

fazster · Answer

Thanks for your help

Excel - copy data macro

6 responses

Similar discussions

Subscribe To Our Newsletter!