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

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kripistipien" <u45702@uwe wrote in message news:892813445e7ff@uwe...
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