How to avoid copying and pasting 100+ worksheets?

[knightshad7]

At work, I am able to download a mailing list from our database, but it always comes out as a pdf. When converting the pdf to Excel, it makes a new worksheet for each page of the pdf. This sometimes results in an Excel workbook with 300+ worksheet tabs at the bottom, each with 50 rows. They all have the same number of columns. Is there a way for me to combine the contents of all the worksheets into one without copying and pasting each individual worksheet?

This is not really a matter of consolidating data, since each row is a unique address. Instead of having 50 rows over 100 worksheets, I would like to have 5,000 rows in one worksheet. Can this be done without VBA? If not, how would I go about doing it in VBA?

Thanks so much in advance!

 

[Leith Ross]

Hello knightshad7,

Add a new VBA Module to your converted workbook and paste the macro code below into it. You can then call the macro using the ALT+F8 keys in Excel. This will copy all the data to a single sheet. The other sheets will remain intact. If you want the macro to remove the other sheets, let me know.

Sub CondenseData()

    Dim DstRng  As Range
    Dim MainWks As Worksheet
    Dim n       As Long
    Dim SrcRng  As Range
    Dim Wks     As Worksheet
    
        Set MainWks = ThisWorkbook.Worksheets(1)
        
        Set SrcRng = MainWks.UsedRange
        Set SrcRng = SrcRng.Offset(SrcRng.Rows.Count, 0)
        
            For n = 2 To ThisWorkbook.Worksheets.Count
                Set Wks = ThisWorkbook.Worksheets(n)
                Set DstRng = Wks.UsedRange
                DstRng.Copy SrcRng
                Set SrcRng = SrcRng.Offset(DstRng.Rows, 0)
            Next n
            
End Sub

How to Add a VBA Module to the Workbook

1. Copy the macro with Ctrl+C.
2. Open the Excel workbook and use Alt+F11 to open the VB Editor.
3. Use ALT+I to display the Insert Menu.
4. Press the M key to add a new Module.
5. Paste the macro into the Module with Ctrl+V.
6. Save the macro using Ctrl+S

 

[knightshad7]

Hello knightshad7,

Add a new VBA Module to your converted workbook and paste the macro code below into it. You can then call the macro using the ALT+F8 keys in Excel. This will copy all the data to a single sheet. The other sheets will remain intact. If you want the macro to remove the other sheets, let me know.

Sub CondenseData()

    Dim DstRng  As Range
    Dim MainWks As Worksheet
    Dim n       As Long
    Dim SrcRng  As Range
    Dim Wks     As Worksheet
    
        Set MainWks = ThisWorkbook.Worksheets(1)
        
        Set SrcRng = MainWks.UsedRange
        Set SrcRng = SrcRng.Offset(SrcRng.Rows.Count, 0)
        
            For n = 2 To ThisWorkbook.Worksheets.Count
                Set Wks = ThisWorkbook.Worksheets(n)
                Set DstRng = Wks.UsedRange
                DstRng.Copy SrcRng
                Set SrcRng = SrcRng.Offset(DstRng.Rows, 0)
            Next n
            
End Sub

How to Add a VBA Module to the Workbook

1. Copy the macro with Ctrl+C.
2. Open the Excel workbook and use Alt+F11 to open the VB Editor.
3. Use ALT+I to display the Insert Menu.
4. Press the M key to add a new Module.
5. Paste the macro into the Module with Ctrl+V.
6. Save the macro using Ctrl+S

Thank you for the response!! This seems super promising, but when I run the Macro, it copies the first sheet into my master sheet and then gives me a message "Run-time error '13' - Type mismatch". Do my sheets need to be formatted a certain way? Right now, each sheet has the same number of columns and rows.

 

[Leith Ross]

Hello Hello knightshad7,

Perhaps I misunderstood your original post. Are you copying data from the converted PDF file to another open workbook?

 

[knightshad7]

Hello Hello knightshad7,

Perhaps I misunderstood your original post. Are you copying data from the converted PDF file to another open workbook?

I have a blank "Master" worksheet in my workbook. I would like to copy and paste the contents of all the rest of the worksheets (sheet 2, sheet 3, sheet 4,...) into my Master sheet.

I appreciate your help with this.

 

[Leith Ross]

Hello Hello knightshad7,

So this "Master" sheet has been added to the workbook created from the PDF file. Did you name the worksheet "Master" or something else. The macro needs to know the worksheet name to copy the data to.

 

[knightshad7]

Yes, the sheet I'm trying to paste it in would be named "Master". Is your code set up to paste the contents into a different workbook? I am not too familiar with VBA and I am struggling to interpret what each line of your code does.

 

[Leith Ross]

Hello Hello knightshad7,

This revision should work now. Each worksheet's name is compared to the master worksheet's name. If the names are different then the data from the worksheet is copied to the Master starting at cell A1.

Sub CondenseData()

    Dim DstRng  As Range
    Dim MainWks As Worksheet
    Dim SrcRng  As Range
    Dim Wks     As Worksheet
    
        Set MainWks = ThisWorkbook.Worksheets("Master")
        
        Set SrcRng = MainWks.Range("A1")
        
            For Each Wks In ThisWorkbook.Worksheets
                If Wks.Name <> MainWks.Name Then
                    Set DstRng = Wks.UsedRange
                    DstRng.Copy SrcRng
                    Set SrcRng = SrcRng.Offset(DstRng.Rows, 0)
                End If
            Next Wks
            
End Sub

 

[knightshad7]

Hello knightshad7,

Add a new VBA Module to your converted workbook and paste the macro code below into it. You can then call the macro using the ALT+F8 keys in Excel. This will copy all the data to a single sheet. The other sheets will remain intact. If you want the macro to remove the other sheets, let me know.

Sub CondenseData()

    Dim DstRng  As Range
    Dim MainWks As Worksheet
    Dim n       As Long
    Dim SrcRng  As Range
    Dim Wks     As Worksheet
    
        Set MainWks = ThisWorkbook.Worksheets(1)
        
        Set SrcRng = MainWks.UsedRange
        Set SrcRng = SrcRng.Offset(SrcRng.Rows.Count, 0)
        
            For n = 2 To ThisWorkbook.Worksheets.Count
                Set Wks = ThisWorkbook.Worksheets(n)
                Set DstRng = Wks.UsedRange
                DstRng.Copy SrcRng
                Set SrcRng = SrcRng.Offset(DstRng.Rows, 0)
            Next n
            
End Sub

How to Add a VBA Module to the Workbook

1. Copy the macro with Ctrl+C.
2. Open the Excel workbook and use Alt+F11 to open the VB Editor.
3. Use ALT+I to display the Insert Menu.
4. Press the M key to add a new Module.
5. Paste the macro into the Module with Ctrl+V.
6. Save the macro using Ctrl+S

Thank you for your help Leith Ross! I figured out why I kept getting a mismatch error. The only change I needed to make to your code was in the 3rd to the last line. It needed to be DstRng.Rows.Count instead of Dst.Rows. Now it does exactly what I wanted. Thanks again!

Sub CondenseData()

    Dim DstRng  As Range
    Dim MainWks As Worksheet
    Dim n       As Long
    Dim SrcRng  As Range
    Dim Wks     As Worksheet
    
        Set MainWks = ThisWorkbook.Worksheets(1)
        
        Set SrcRng = MainWks.UsedRange
        Set SrcRng = SrcRng.Offset(SrcRng.Rows.Count, 0)
        
            For n = 2 To ThisWorkbook.Worksheets.Count
                Set Wks = ThisWorkbook.Worksheets(n)
                Set DstRng = Wks.UsedRange
                DstRng.Copy SrcRng
                Set SrcRng = SrcRng.Offset(DstRng.Rows.Count, 0)
            Next n
            
End Sub