Macro Excel to automatise the copy of data from one worksheet to another sheet

[Kripistipien]

Hi,

I am looking for aid with the following macro:
- excel file with 5 worksheets
- 4 worksheets contain data which has to be automatically copied to the 5th
worksheet each time you run the macro, or each time you press some button

I tried to record a macro this way:
- go to sheet 1, put mouse on first cel, push control+shift+end
- then i do control+c
- i go to sheet 5 (the "total" sheet) and push control+v
--> the problem: how can i make excel understand he has to put the mouse in
the cell after the last cell of the data which i just copied?
--> other problem: does control+shift+end depend on the amount of rows i copy?
i do not want a certain of rows to be copied. the sheets are updated by
someone else, so i don't know how many rows he added or deleted. excel can
therefore not always copy the exact amount of rows i copied recording the
macro....

Please help me out if someone knows how to do this...

Thanks in advance for any suggestions, advice..

Kristien

 

[Don Guillett]

something like this should do it. Change "f" to suit your source column

sub (copyshtstototalsht)
for each ws in activeworkbook.worksheets
if ws.name <>"Total" then
with ws
slr=.cells(rows.count,"a").end(xlup).row
dlr=sheet("Total").cells(rows.count,"a").end(xlup).row+1
..range(cells(2,"a"),cells(slr,"f")).copy sheets("Total").cells(dlr,"a")
end with
next ws
end sub