Copying data from several worksheets to one worksheet

[Ben Hur]

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

 

[Ron de Bruin]

Hi Ben

Maybe you can use this
http://www.rondebruin.nl/copy2.htm

 

[Tom Ogilvy]

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

 

[Ben Hur]

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.

 

[Tom Ogilvy]

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

 

[gabor salai]

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