Consolidate Worksheet Data

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Hi -

I'm using Excel 97 in Windows 2000.

I have a workbook that has 50 sheets. Each sheet has
information about one of my stores. They are all
basically identical in layout & type of data. For
example, Col A holds Employee ID, Col B holds Employee
Name, and Col C holds Salary. I don't have more than 10
rows per sheet. I'd like to build a macro that would copy
all of this data and paste it into a "total company"
worksheet. Any suggestions? Thanks in advance! Matt
 
Matt,

Something like this (untested). Amend to fit your needs.

Dim pasterow as Long, copyrow as Long, x as Integer
Application.ScreenUpdating = False

'Use this section to clear the Company sheet
''''''''''''''''''''''''''''''''''''''''
pasterow = Sheets("Company").Cells(Rows.COUNT, "A").End(xlUp).Row '
[fix word wrap]
Sheets("Company").Range(Rows(2),Rows(pasterow)).ClearContents
''''''''''''''''''''''''''''''''''''''''

For x = Sheets("Sheet1").Index to Sheets("Sheet50").Index
Sheets(x).Select
pasterow = Sheets("Company").Cells(Rows.COUNT, "A").End(xlUp).Row '
[fix word wrap]
copyrow = Sheets(x).Cells(Rows.COUNT, "A").End(xlUp).Row

Sheets(x).Range(Rows(2),Rows(copyrow)).Copy _
Destination:= Sheets("Company").Cells(pasterow,1)
Next

Sheets("Company").Select
Application.ScreenUpdating = True
 
Back
Top