Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine Data from Multiple Worksheets (2003)

    I have an Excel workbook that contains over 60 worksheets. Each sheet has the same type of data in columns A-H. Each sheet contains between 1 and 4000 rows of data. I need to create a single sheet (or workbook) that will contain all rows of data from all worksheets. The end result I will need is a comma delimited file with all data. I can create this is it is easier to combine all data on a new sheet in the current workbook.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combine Data from Multiple Worksheets (2003)

    Hi Randy

    Will this do the job...enjoy

    Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
    Sheets(J).Activate ' make the sheet active
    Range("A1").Select
    Selection.CurrentRegion.Select ' select all cells in this sheets

    ' select all lines except title
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

    ' copy cells selected in the new sheet on last line
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2 )
    Next
    End Sub
    Jerry

  3. #3
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Data from Multiple Worksheets (2003)

    Almost perfect. Each sheet does not have a header row or title row. I can add to all sheets at one time, however, I don't get a title row created when this spreadsheet is generated from another system. Can it pick up the data starting with row 1?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine Data from Multiple Worksheets (2003)

    Jezza has indicated in his code where he copies the title row in Sheet 2, and where he omits copying the title row in the loop, so it shouldn't be hard to modify his macro.

  5. #5
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Data from Multiple Worksheets (2003)

    That works great! I am a little code lacking still, but learning from all the great help in here!

    Thanks to both <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

Posting Permissions

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