Multiple Tabs into One

[jsimps76]

I have a massive amount of data in an excel spreadsheet that spans about 500 different tabs. How can I consolidate those into one single tab without the copy paste function which would take an entire working day for me?

 

[Brettster]

The below will do what you need:

Option Explicit

Sub CopyOverSheetData()

Dim ws As Worksheet
Dim wsCopy As Worksheet
Dim rngUsed As Range
Dim rngPaste As Range

'The activesheet is the sheet where data will be pasted!
Set wsCopy = ActiveSheet
Set rngPaste = wsCopy.Range("A1")

'Disable screenupdating and set calculation to manual mode
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each ws In ThisWorkbook.Worksheets
    
    If ws.Name <> wsCopy.Name Then
    
        Set rngUsed = ws.UsedRange
        
        'Copy and paste data
        rngUsed.Copy
        rngPaste.PasteSpecial (xlPasteAll) 'Change parameter to "xlPasteValuesAndNumberFormats" if you only want values
        Application.CutCopyMode = False
        
        'Offset paste range ready for next sheet
        Set rngPaste = rngPaste.Offset(rngUsed.Rows.Count)
    
    End If
    
Next ws

'Reset screenupdating and calculation mode
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Done!"

End Sub

Hope this helps.

 

[dazfoz]

Brett,

Thanks for this, just saved me an age.

However, how could I define the copy range for each tab to start on say row 11. (So that the code will only copy row 11 down into the new page).

ws.UsedRange always selectes the entire page.

Apologies for dragging an old thread up...

Regards