Hello.
I have converted pdf files into excel. So each pdf file presents sheets in a single workbook.
Now the issue is I have around 100 sheets. But I want all the data one below another rather than on different sheets.
Copy paste is one option , but it is time consuming & not suitable for large data.
Looking forward for a solution.
Thank you.
For the code below to work I assumed the following:
There is data in cell A1 and the rest of the data is connected to it. By which I mean that there is data to the cell next to the cell with data in it. When you have data in range A1:A10 and C1:C10, then column C won't get copied.
The total amount of data rows doesn't exceed the maximum number of rows of a single sheet.
Altering the code:
I named the sheet where all the data is copied to "Master". Change this to match your destination sheet name on code line 5.
When you want an empty row between the data from each sheet then change Offset(1, 0) into Offset(2, 0) on code line 11.
Give the code a try and see if it yields the desired result.
Sub RunMe()
Dim sh As Worksheet
Dim dSheet As String
dSheet = "Master"
For Each sh In Worksheets
If Not sh.Name = dSheet Then
sh.Select
Range("A1").CurrentRegion.Copy _
Sheets(dSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next sh
Sheets(dSheet).Select
End Sub
