Merge multiple worksheets into one?

[krystyne20]

Is it possible to merge multiple worksheets that are in the same workbook, into one long worksheet?

What happened is, I converted a 131 page pdf table into excel, but instead of getting one long excel spreadsheet, each page was put onto a different worksheet. So I now have an Excel document with 131 worksheet tabs. Is there a simple way to merge/consolidate them into the same worksheet so that it's one continuous spreadsheet?

 

[Domski]

Hi & welcome.

No simple way, you'd need to use VBA really without copying and pasting manually. This sort of question has been asked a lot before. Have you done a search to see if there's a previous post that answers your question?

If you don't find anything can you give a little more detail. Are there headings on each sheet for example?

Dom

 

[Domski]

I'm off to bed so in case you don't find anything this may work for you:

Sub Combine_Sheets()

Dim lastRow As Long, mySheet As Long, writeRow As Long

Worksheets.Add Before:=Sheets(1)

writeRow = 1

For mySheet = 2 To Worksheets.Count

    lastRow = Sheets(mySheet).Cells(Rows.Count, 1).End(xlUp).Row
    
    Sheets(mySheet).Rows("2:" & lastRow).Copy Sheets(1).Range("A" & writeRow)
    
    writeRow = writeRow + lastRow - 1

Next mySheet

End Sub

Pop the above code in a Module in the workbook's VBA project and it will add a sheet at the start of your sheet tabs and copy data off all the ones after onto it. It assumes you've got heading on each sheet.

Post back if it's not what you're after.

Dom

 

[padre310]

This there an adaptation to this that would put the information side by side instead on stacking it? Also each sheet has its own headers. I need to maintain that information. Thanks.

I'm off to bed so in case you don't find anything this may work for you:

Sub Combine_Sheets()
 
Dim lastRow As Long, mySheet As Long, writeRow As Long
 
Worksheets.Add Before:=Sheets(1)
 
writeRow = 1
 
For mySheet = 2 To Worksheets.Count
 
    lastRow = Sheets(mySheet).Cells(Rows.Count, 1).End(xlUp).Row
 
    Sheets(mySheet).Rows("2:" & lastRow).Copy Sheets(1).Range("A" & writeRow)
 
    writeRow = writeRow + lastRow - 1
 
Next mySheet
 
End Sub

Pop the above code in a Module in the workbook's VBA project and it will add a sheet at the start of your sheet tabs and copy data off all the ones after onto it. It assumes you've got heading on each sheet.

Post back if it's not what you're after.

Dom

 

[lapta301]

krystyne20 & Domski


Forgive me intruding but I have a similar problem. I want to define the last row on each sheet by the last entry in column D. I have formulas populating columns A-C for rows that are otherwise empty.
Can you advise how this can be achieved.

By the way other than bringing in dozens of empty rows the code works fine.

Thank you.


Lapta301