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

 

[mrsect]

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

yup, the for each statement has an error "Object doesn't support this property or method"

 

[mumps]

Are any of the sheets in the source workbook blank with no data? Perhaps you could upload a copies of your master file and source file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

 

[Saurabhj]

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

Yes, In Power Query it's simple.
After Step 14 -
To replace all null values to space/ 0 select a column and Replace Values option in Transform area.

 

[mrsect]

ok so I got his to add all the headers to one sheet but hen it stops after the headers and says subscript is out of range but I am running the macro inside the only workbook that is open




Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")

Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)

'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column

Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws

Worksheets("Master").Activate

End Sub

 

[mumps]

Your original thread says that you want to copy from one workbook to another workbook. Has that changed now and you want to copy from all sheets in the same workbook to the Master sheet in that workbook? It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[mrsect]

Your original thread says that you want to copy from one workbook to another workbook. Has that changed now and you want to copy from all sheets in the same workbook to the Master sheet in that workbook? It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

No, that hasn't changed just trying something different just to see if I can do it, ok will do that would one drive work?

 

[mumps]

It should.

 

[mrsect]

It should.

Here is the link let me know if you want the master workbook its just a blank workbook called master

https://accuratecontrolsinc-my.shar...pIp9mQ4rPr43IBC-uEW-ElTtfoHqTHDOL_RA?e=tRYrY1

 

[mumps]

Your link takes me to the sign-in page. I need a direct link to your file.

 

[mrsect]

Your link takes me to the sign-in page. I need a direct link to your file.

what about this one? 7.27.2020.xlsx