Copy data from all sheets and specific range into master workbook

[mrsect]

Hello,

I have a workbook that contains multiple sheets, each sheet is named differently but each sheet contains the same type of data looking to select all the sheets and copy data from all sheets range a-h and paste into master workbook

 

[Saurabhj]

Use Power Query to merge all sheets in a single sheet.

Follow below steps:

1. Goto Data tab and under get & Transform group, click on arrow next to Get Data.
2. Click on From File and select From Workbook option.
3. Select the required excel file in which multiple sheets are available and press Import button.
4. It will Open Navigator window.
5. In Navigator window, select checkbox for "Select Multiple Files".
6. Select all the sheets which you want to append/ merge.
7. Click Transform Data button.
8. In Power Query editor, click on Append Queries option under Combine Area.
9. It will open Append window.
10. In Append window, select 3 or more tables as you have more than 2 sheets.
11. By default, sheet1 is selected as current and power query will follow the same sheet as template.
12. Select other sheets also and press Add button. All sheets now in RHS list box.
13. Click Ok.
14. The data section is now showing the merged data. If you need any transformation/ changes in the data you can do the same.
15. Click Close & load button.

And all sheets will be merge in single sheet.

 

[mrsect]

thank you for the quick response is there a way to take out the null spaces?

 

[mumps]

Do you want to copy the data from all the sheets to one sheet in the Master or on separate sheets? What is the full name including extension of the source workbook?

take out the null spaces

Can you explain what you mean by this statement?

 

[mrsect]

Do you want to copy the data from all the sheets to one sheet in the Master or on separate sheets? What is the full name including extension of the source workbook?

Can you explain what you mean by this statement?

I want to copy the data from all the sheets to one master sheet. The source workbook name is 7.27.20.xlsx , the comment means when I combine in the query there was 20 rows with no data and show null in the output

 

[mumps]

Make sure that both workbooks are open. Place this macro in the master workbook and make sure that it contains a sheet named "Master". Run the macro.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWB As Workbook, desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Master")
    Set srcWB = Workbooks("7.27.20.xlsx")
    For Each ws In srcWB
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("A1:H" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
    Next ws
    desWS.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[mrsect]

Make sure that both workbooks are open. Place this macro in the master workbook and make sure that it contains a sheet named "Master". Run the macro.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWB As Workbook, desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Master")
    Set srcWB = Workbooks("7.27.20.xlsx")
    For Each ws In srcWB
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("A1:H" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
    Next ws
    desWS.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[mrsect]

Make sure that both workbooks are open. Place this macro in the master workbook and make sure that it contains a sheet named "Master". Run the macro.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWB As Workbook, desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Master")
    Set srcWB = Workbooks("7.27.20.xlsx")
    For Each ws In srcWB
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("A1:H" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
    Next ws
    desWS.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

thank you,

However after running code I received an error on "Set srcWB = Workbooks("7.27.20.xlsx")"

 

[mrsect]

thank you,

However after running code I received an error on "Set srcWB = Workbooks("7.27.20.xlsx")"

Subscript out of range is the error

 

[mumps]

That would indicate that the workbook is not open or the workbook name is not correct.