Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from several worksheets to one worksheet
Hi,
I have been given a task to create a master sheet from several Excel worksheets (where a database would probably be more use, but hey). I'm assuming I'll need a macro for this, as I can't think of a formula that would work. The workbook consists of seven worksheets + a master worksheet. All have the same headings and contain smilar data. I need to be able to scan through Sheet1 until I find the first empty row and then copy the data to the Master sheet. Then I need to perform the same task on Sheet2 and paste the data in the Master sheet underneath the data copied and pasted from Sheet 1. This needs to be repeated for all seven sheets. Secondly, when the macro is run again (if a user has updated one of the sheets) it must not re-copy and paste the existing data. Maybe it would be easier to delete the original data and then re-copy all the new data. Thanks for any help offered, BH |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from several worksheets to one worksheet
Hi Ben
Maybe you can use this http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Ben Hur" wrote in message ... Hi, I have been given a task to create a master sheet from several Excel worksheets (where a database would probably be more use, but hey). I'm assuming I'll need a macro for this, as I can't think of a formula that would work. The workbook consists of seven worksheets + a master worksheet. All have the same headings and contain smilar data. I need to be able to scan through Sheet1 until I find the first empty row and then copy the data to the Master sheet. Then I need to perform the same task on Sheet2 and paste the data in the Master sheet underneath the data copied and pasted from Sheet 1. This needs to be repeated for all seven sheets. Secondly, when the macro is run again (if a user has updated one of the sheets) it must not re-copy and paste the existing data. Maybe it would be easier to delete the original data and then re-copy all the new data. Thanks for any help offered, BH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from several worksheets to one worksheet
Dim sh as Worksheet
With Worksheets("Master") .Cells.ClearContents for each sh in Worksheets if lcase(sh.Name) < "master" then sh.Range("A1").CurrentRegion.Copy _ Destination .Cells(rows.count,1).End(xlup)(2) end if Next End With -- Regards, Tom Ogilvy "Ben Hur" wrote in message ... Hi, I have been given a task to create a master sheet from several Excel worksheets (where a database would probably be more use, but hey). I'm assuming I'll need a macro for this, as I can't think of a formula that would work. The workbook consists of seven worksheets + a master worksheet. All have the same headings and contain smilar data. I need to be able to scan through Sheet1 until I find the first empty row and then copy the data to the Master sheet. Then I need to perform the same task on Sheet2 and paste the data in the Master sheet underneath the data copied and pasted from Sheet 1. This needs to be repeated for all seven sheets. Secondly, when the macro is run again (if a user has updated one of the sheets) it must not re-copy and paste the existing data. Maybe it would be easier to delete the original data and then re-copy all the new data. Thanks for any help offered, BH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from several worksheets to one worksheet
Thanks for your help Tom and Ron, however I cannot get either of your
macros to work. Tom's macro gives an error, while Ron's macros don't seem to pick up the data (I'm sure due to my programming skills rather than problems with your code) I need to copy the range A7:last row from Sheet1 and then paste on master sheet rows A7:n, followed by A7:last row from Sheet2 and then paste on master sheet rows below the data from Sheet1. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from several worksheets to one worksheet
there was a typo in my code:
Sub BB() Dim sh As Worksheet With Worksheets("Master") .Cells.ClearContents For Each sh In Worksheets If LCase(sh.Name) < "master" Then sh.Range("A1").CurrentRegion.Copy _ Destination:=.Cells(Rows.Count, 1).End(xlUp)(2) End If Next End With End Sub -- Regards, Tom Ogilvy "Ben Hur" wrote in message om... Thanks for your help Tom and Ron, however I cannot get either of your macros to work. Tom's macro gives an error, while Ron's macros don't seem to pick up the data (I'm sure due to my programming skills rather than problems with your code) I need to copy the range A7:last row from Sheet1 and then paste on master sheet rows A7:n, followed by A7:last row from Sheet2 and then paste on master sheet rows below the data from Sheet1. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from several worksheets to one worksheet
"Tom Ogilvy" wrote in message
... there was a typo in my code: Sub BB() Dim sh As Worksheet With Worksheets("Master") .Cells.ClearContents For Each sh In Worksheets If LCase(sh.Name) < "master" Then just as question: isn't it more safe to test worksheets object equality, ("is" operator) like: set master=worksheets("master") with master for each sh in worksheets if not master is sh then 'don't have to check strings! ... end if next end with |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying from one worksheet to multiple worksheets | Excel Discussion (Misc queries) | |||
Copying excel worksheets to new worksheet | Excel Discussion (Misc queries) | |||
Copying fixed cell value from different worksheets to new worksheet | Excel Discussion (Misc queries) | |||
Copying changes from one worksheet to other worksheets | Excel Worksheet Functions | |||
Copying data from several worksheets and merging into a new worksheet | Excel Programming |