Merging multiple sheets into one sheet

[J]

This data is not exactly the same on each sheet. The columns are the
same but the data starts on different rows. I need to put the first
three columns on one sheet. I'm not having any luck. The rows are
variable too but not > 60. I don't care how it looks, it just needs to
be aligned in the same columns. There are about 100 sheets.

Ultimately, I am going to add a column that will put a D or P if the
order numbers are in a Delivery or Pickup. Then compare the number
with another spreadsheet and return the date. I'll be able to do this
with a lookup, I've tested it a smaller set and it works ok.
If anyone has another suggestion I'm open to that too.

Sample of the data

A B C
DELIVERY-SCHEDULE "C"
DATE ORDER # WEIGHT
10/1/04 43328 878
10/2/04 45468 250
TOTAL

DELIVERY-SCHEDULE "D"
DATE ORDER # WEIGHT
9/28/04 40182 1419
9/30/04 44389 1058
10/1/04 30024 650
10/1/04 174566
TOTAL

DELIVERY-SCHEDULE "E"
DATE ORDER # WEIGHT
9/28/04 174884 135
9/28/04 39674 261
9/29/04 43737 602
9/29/04 45318 817
9/29/04 42766 3009
9/29/04 39657 954
10/1/04 41022 487
TOTAL

PICKUP-SCHEDULE "C"
DATE ORDER # WEIGHT
9/25/04 35055 246
10/2/04 44476 388
10/2/04 46111 1471

 

[Ken Wright]

Slightly confused how all your columns can be the same if Delivery Schedule
turns into Pick Up Schedule, but assuming you only have data sheets in your
file, as all will be included, then try this:-

Sub CombineSheets()

Dim SumWks As Worksheet
Dim sd As Worksheet
Dim Sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

HeadRow = InputBox("What row are the Sheet's data headers in?")
DataRow = HeadRow + 1

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set SumWks = Worksheets.Add

With SumWks
.Move Before:=Sheets(1)
.Name = "Summary Sheet"
Sheets(2).Rows(HeadRow).Copy .Range("1:1")
Columns("A:A").Insert Shift:=xlToRight
Range("A1").Value = "INDEX"
End With

With Sheets(2)
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count

End With

For Sht = 2 To ActiveWorkbook.Sheets.Count
Set sd = Sheets(Sht)
lrow1 = SumWks.Cells(Rows.Count, "B").End(xlUp).Row
lrow2 = sd.Cells(Rows.Count, "A").End(xlUp).Row
sd.Activate
sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1,
2)
SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = sd.Name
Next Sht

SumWks.Activate

End Sub

Then just delete the Columns you don't want

 

[J]

Hi Ken,


Thanks for the help.

Couple of problems -- see below

Sub CombineSheets()

Dim SumWks As Worksheet
Dim sd As Worksheet
Dim Sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

HeadRow = InputBox("What row are the Sheet's data headers in?")
DataRow = HeadRow + 1

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set SumWks = Worksheets.Add

With SumWks
.Move Before:=Sheets(1)
.Name = "Summary Sheet"
Sheets(2).Rows(HeadRow).Copy .Range("1:1")
Columns("A:A").Insert Shift:=xlToRight
Range("A1").Value = "INDEX"
End With

With Sheets(2)
ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count

End With

For Sht = 2 To ActiveWorkbook.Sheets.Count
Set sd = Sheets(Sht)
lrow1 = SumWks.Cells(Rows.Count, "B").End(xlUp).Row
lrow2 = sd.Cells(Rows.Count, "A").End(xlUp).Row
sd.Activate
sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1,
2)

Seems to bomb right here. Run time 1004 app. defind error, and
debugger prints Method 'Range' of object '_Worksheet' failed.
I tried adding on error resume next but in only prints sd.name. I
don't really understand the line above and below my text. Not really
sure what's going on with .End(xlUp).row. Is that the last row of the
sheet -1? It showed 65535 but then just 60. I don't know how it got
only used cells for the rows.

Each sheet is about forty rows long. Can I put increment a range by
forty? So if paste to range 1 to 40, then next past is 40 - 80.
Something like that, perhaps? I'm going to play around with it a bit
and see if I can get 'er to fly.
Thanks for the help

 

[Chip Pearson]

Your code will blow up on the line
sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy
SumWks.Cells(lrow1 + 1, 2)

if sd is not the active sheet.. The reason is that you are
attempting to create a Range object on sd, but the Cells property
is not qualified and thus refers to the active sheet. Try
rewriting the code like

With sd
.Range(.Cells(DataRow,1),.Cells(lrow2,ColW)).Copy
SumWks.Cells(lrow1+1,2)
End With

Note the leading periods before Range and Cells.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Ken Wright]

Chip - Thanks for that and my bad - I did have the code activate that sheet in
the loop so it may be wordwrap bombing it out, but I would have been better off
qualifying it :-(

J - The other thing to watch for is wordwrap on the code, as that last line
should have been a whole line and not have been split across 3 rows

still subject to Chips corrections of course

On the one line there will be a single space between the )).copy from the first
line and the SumWks.Cells from the next. Don't worry about how many rows there
are in the data, the code uses a pretty standard method to find out what the
last used row on each sheet is and then only pulls across that data, eg if you
have 100 rows on the first 250 on the second, 12 on the third and so on then
those are the only rows that get pulled across.

I'm still not sure how your data is standard though if your headings are
different as in your example. What this code will do is to take your file,
insert a new sheet and call it Summary, then copy in the headers from sheet 2
(irrelevant which sheet as it assumes that are all the same), then insert a new
column on the summary sheet and label it INDEX. From there it then copies in
the data from each sheet and labels that data in the INDEX column with the Sheet
name it has copied it from. The reason for this is that if your sheets are laid
out such as Week1, Week2, Week3 etc then you can now use the INDEX field as a
field within a Pivot table to summarise data by.

Also, if you have ANY other sheets in this file that are not to be treated like
this (eg some other kind of summary sheet, or calculation sheet), then you will
need to strip that out of the finished data on the Summary sheet. I made the
assumption that you ONLY have the raw data sheets in this file.

 

[J]

Thanks guys, really appreciate your help.

J - The other thing to watch for is wordwrap on the code, as that last line
should have been a whole line and not have been split across 3 rows

Yes, I did catch that.

I'm still not sure how your data is standard though if your headings are
different as in your example.

It isn't. It's a huge mess. I'm just trying to make do with the doo I have.
Turns out its worse than I thought.