Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combining Worksheets (2000)

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

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
    wkb.Worksheets.Add after:=Worksheets(y)
    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
  •