1. ## Combining Worksheets (2000)

We use a program called Impromptu to create reports.
Once the report is created, we save the report as Excel.
When the report is saved as Excel using Impromptu, it puts the data in multiple worksheets, not filling up the first worksheet first and then going to another worksheet.
Once the data is saved as Excel (in these multiple worksheets), I would like to run some kind of a macro that takes the data and fills up the first worksheet and goes to the next and fills it up etc.
Is there a macro that can do this?
Right now the data is spread across say 10 worksheets, when it could be only 2 worksheets.
We manually go in and highlight the data in sheet 2 and copy it into sheet 1 etc etc.

Any help would be greatly appreciated - thank you.

2. ## Re: Combining Worksheets (2000)

How can we determine the "used range" in each worksheet? Is there a column that is always populated, for example?
What are the names of the worksheets? Are they named Sheet1, Sheet2 etc., or ...?

3. ## Re: Combining Worksheets (2000)

The sheets are Sheet1, Sheet2 etc - the normal name of sheets. Impromptu saves the data to Excel when Excel only had 16000 (I don't know the exact number of rows) rows a sheet. Now that Excel has 65000(I don't know exact number), it still saves it as the number of rows an older version of Excel had. That number is consistant for each worksheet except the last. So the used range in the majority of the worksheets would be the number of rows that the older version of Excel had.

4. ## Re: Combining Worksheets (2000)

Thanks for the information.
We still have to determine how many rows there are on the last sheet. Is there a column that is guaranteed to have an entry in the last used row?

5. ## Re: Combining Worksheets (2000)

That last sheet could be any number of rows less than the 16,384 (figured out number of rows). Right now I can combines 4 (16,384 rows) to create one (65,536) worksheet in Excel 2000. The last worksheet will always be what is less than the 16,384 default rows)

6. ## Re: Combining Worksheets (2000)

I understand that, but I was wondering if there is an easy way to find the last used row in the last sheet. For example, will the cell in column A in the last row always be non-blank, or the cell in column B, etc., or is that unpredictable?

7. ## Re: Combining Worksheets (2000)

If I had to pick a cell that is non-blank the majority of the time in the last row, it would be the last cell in Column B. I hope this is what you mean.

8. ## Re: Combining Worksheets (2000)

"Majority of the time" is not good enough. We'll take the brute force approach. Try this code - make a backup copy before you do so!

Sub MoveCells()
Dim lngSource As Long
Dim lngTarget As Long
Dim lngSheets As Long
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
lngSheets = ActiveWorkbook.Worksheets.Count
For lngSource = 2 To lngSheets
Set wshSource = ActiveWorkbook.Worksheets("Sheet" & lngSource)
lngTarget = (lngSource - 1) 4 + 1
Set wshTarget = ActiveWorkbook.Worksheets("Sheet" & lngTarget)
wshSource.Range("1:16384").Cut _
Destination:=wshTarget.Cells(((lngSource - 1) Mod 4) * 16384 + 1, 1)
Next lngSource
Set wshTarget = Nothing
Set wshSource = Nothing
End Sub

9. ## Re: Combining Worksheets (2000)

Thanks Hans, I will try it now. If it works the majority of the time, this will be so much easier than highlighting and appending. Thanks so much for your help.

10. ## Re: Combining Worksheets (2000)

Should this code line lngTarget = (lngSource - 1) 4 + 1 not be lngTarget = (lngSource - 1) / 4 + 1.
I speak under correction ( / )???

11. ## Re: Combining Worksheets (2000)

Hi Rudi,
is the integer division operator which returns an integer (and truncates any fractional part). Therefore 1 4 will return 0, whereas 1 / 4 will return 0.25.
Hope that helps.

12. ## Re: Combining Worksheets (2000)

<img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
Taught me something new rory
Thanx

13. ## Re: Combining Worksheets (2000)

I went the generic route, copying each sheet and checking the rows added in case the sheets had different numbers of rows. Mine does it all into a new workbook, so the original is not affected.

<pre>Option Explicit
Sub CombineAllSheets()
Dim wkb As Workbook
Dim wkbActive As Workbook
Dim wks As Worksheet
Dim x As Integer
Dim y As Integer
Dim lLastRow As Long
Dim lTotalRows As Long
Dim lMaxRows As Long

lMaxRows = 65536
Set wkbActive = ActiveWorkbook
With wkbActive
.Worksheets(1).Copy
Set wkb = ActiveWorkbook
y = 1
If .Worksheets.Count > 1 Then
For x = 2 To .Worksheets.Count
lTotalRows = wkb.Worksheets(y).Range("A1"). _
SpecialCells(xlCellTypeLastCell).Row
lLastRow = .Worksheets(x).Range("A1"). _
SpecialCells(xlCellTypeLastCell).Row
If lTotalRows + lLastRow > lMaxRows Then
y = y + 1
lTotalRows = 1
End If
With .Worksheets(x)
.Range(.Range("a1"), _
.Range("a1").SpecialCells(xlCellTypeLastCell)). _
Copy wkb.Worksheets(y).Cells(lTotalRows + 1, 1)
End With
Next
End If
End With
End Sub</pre>

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•