Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple sheets into one sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple sheets into one sheet
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 Befo=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 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "J" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple sheets into one sheet
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 Befo=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 SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = sd.Name Next Sht SumWks.Activate End Sub "J" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple sheets into one sheet
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 "J" wrote in message om... 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 Befo=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 SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = sd.Name Next Sht SumWks.Activate End Sub "J" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple sheets into one sheet
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 sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1, 2) 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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Chip Pearson" wrote in message ... 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 "J" wrote in message om... 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 Befo=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 SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = sd.Name Next Sht SumWks.Activate End Sub "J" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 24/11/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging multiple sheets into one sheet
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 sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1, 2) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summary sheet across multiple sheets | Excel Discussion (Misc queries) | |||
Merging multiple sheets into one sheet without overwriting data. | Excel Worksheet Functions | |||
multiple sheets vs. 1 sheet | Excel Worksheet Functions | |||
splitting 1 sheet to multiple sheets | Excel Discussion (Misc queries) | |||
Combine multiple sheets into one sheet? | Excel Discussion (Misc queries) |