Macro to combine worksheets data - overwrites existing

[sbitaxi]

Hello:

I've written this macro (with a reasonable amount of support from this
group already) to copy data from several worksheets and paste it into
a "Master" worksheet. However, I've made a few changes to the
spreadsheet (rows for the header and a few additional columns) and
instead of appending data, it overwrites everything. How do I tell it
to go to the last row containing data?

Sub UpdateMaster()
'This macro updates the Master Spreadsheet, it is linked to a button
on the Reports tab

' Clears existing data on Master spreadsheet, except for header
row
Sheets("Master").Select
Application.Calculation = xlCalculationManual
Range("A3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents

' Copies data from each spreadsheet in the workbook, excluding
sheets named in Case, and pastes data in Master
For Each thing In Sheets
Select Case thing.Name
Case "Master", "Reports", "Test Filter", "ReportOutput",
"Diagram", "Master2"
'do nothing
Case Else
Sheets(thing.Name).Range("3:1000").Copy

Sheets("Master").Range("A65536").End(xlUp).Offset(1).PasteSpecial
End Select
Next
Sheets("Master").Activate
Range("A3").Select
End Sub

 

[Ron de Bruin]

Hi

See this page for example code
http://www.rondebruin.nl/copy2.htm

 

[sbitaxi]

Hi,

Thanks for this Ron, but I am having trouble with your "LastRow"
command. I get a "Sub or Function not defined" error.

I simply copied your code directly.


Using Excel 2002, SP3


S

 

[Ron de Bruin]

It is simple Copy the LastRow function also in the module



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi,

Thanks for this Ron, but I am having trouble with your "LastRow"
command. I get a "Sub or Function not defined" error.

I simply copied your code directly.


Using Excel 2002, SP3


S

 

[sbitaxi]

Thank you Ron! I just need to read all the text before I try and
decipher the code. I will try to be more vigilant in the future.

This looks great.


S

 

[Ron de Bruin]

You are not the only one that not read the info on my pages <g>

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Thank you Ron! I just need to read all the text before I try and
decipher the code. I will try to be more vigilant in the future.

This looks great.


S