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

Hi

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




--

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


wrote in message ...
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

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

On May 1, 3:03*pm, "Ron de Bruin" wrote:
Hi

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



wrote in ...
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- Hide quoted text -

- Show quoted text -

It is simple Copy the LastRow function also in the module



--

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


wrote in message ...
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

On May 1, 3:03 pm, "Ron de Bruin" wrote:
Hi

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



wrote in ...
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- Hide quoted text -

- Show quoted text -

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

On May 1, 3:39*pm, "Ron de Bruin" wrote:
It is simple Copy the LastRow function also in the module

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ...

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

On May 1, 3:03 pm, "Ron de Bruin" wrote:



Hi

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ...
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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


wrote in message ...
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

On May 1, 3:39 pm, "Ron de Bruin" wrote:
It is simple Copy the LastRow function also in the module

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ...

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

On May 1, 3:03 pm, "Ron de Bruin" wrote:



Hi

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ...
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -