Transfer data between excel sheets

[Closed]
Report
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
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.

1 reply

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Bridge,

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


Best regards,
Trowa