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