VBA to copy data from multiple work book to one work book

[Mr_A]

hello,
I want macro for copy data from multiple(nearly 100) workbook placed in one folder to one master workbook with detail bellow.
All work books having sheet name "list" from that sheet I want to copy cells C9, H9, H10 and H24:H29 from which H24:H29 contains formulas but in master work book i want to copy values only, I try many macro but any available macro not fulfilled my requirement.

please let me know if you require any clarification
Thanks

 

[JLGWhiz]

Which sheet in the master workbook and which cells do you want the copied data pasted to?

 

[jdellasala]

Assuming all of the worksheets have the same format - i.e. Columns, it is MUCH easier to use Power Query to combine all of the data from the other worksheets into a single Table (or Pivot Table or Pivot Table & Pivot Chart). Mr. E has a video for exactly that process: Suddenly It's Here: Combine Sheets in Power Query - 2295.
I used Excel for decades (and Spreadsheets since the 80's!), and Power Query would have saved me thousands of hours of work had I discovered it sooner. I only started using it in late 2018.
Once completed, if more worksheets are added, you only need to refresh the data to update the combination worksheet.
Assuming you combine all the worksheets into a new worksheet named "Master", don't forget to filter it out at the start of the query (the same way the video filters out the sheet "NotThis") or you'll duplicate what's already there when you Refresh! This is something worth the time to learn!

 

[Mr_A]

Which sheet in the master workbook and which cells do you want the copied data pasted to?

Summary sheet in master workbook and data past from b2, c2, c3 to a raw for a file and other in sub sequences rows i.e b2, b3, b4
Addition required link with file name on first column if possible.

 

[Mr_A]

Assuming all of the worksheets have the same format - i.e. Columns, it is MUCH easier to use Power Query to combine all of the data from the other worksheets into a single Table (or Pivot Table or Pivot Table & Pivot Chart). Mr. E has a video for exactly that process: Suddenly It's Here: Combine Sheets in Power Query - 2295.
I used Excel for decades (and Spreadsheets since the 80's!), and Power Query would have saved me thousands of hours of work had I discovered it sooner. I only started using it in late 2018.
Once completed, if more worksheets are added, you only need to refresh the data to update the combination worksheet.
Assuming you combine all the worksheets into a new worksheet named "Master", don't forget to filter it out at the start of the query (the same way the video filters out the sheet "NotThis") or you'll duplicate what's already there when you Refresh! This is something worth the time to learn!

Ok, but power query is suitable for data from table format but I am having data in form format from which I want to copy cells C9, H9, H10 and H24:H29 from all the work books, from which H24:H29 contains formulas but in master work book I want to copy values only

Additionally required link with file name on first column if possible.

 

[JLGWhiz]

Summary sheet in master workbook and data past from b2, c2, c3 to a raw for a file and other in sub sequences rows i.e b2, b3, b4
Addition required link with file name on first column if possible.

Destination cells are not matcdhing source cells, so it can't be coded like this. Is this what you want your summary sheet to look like?

TestBase.xlsm
	A	B	C	D
1
2		sh1!C9	sh1!H9
3			sh1!H10
4			sh1!H24
5			sh1!H25
6			sh1!H26
7			sh1!H27
8			sh1!H28
9			sh1!H29
10		sh2!C9	sh2!H9
11			sh21!H10
12			sh2!H24
13			sh2!H25
14			sh2!H26
15			sh2!H27
16			sh2!H28
17			sh2!H29
18		sh3!C9	sh3!H9
19			sh3!H10
20			sh3!H24
21			sh3!H25
22			sh3!H26
23			sh3!H27
24			sh3!H28
25			sh3!H29
Sheet1

 

[Mr_A]

Destination cells are not matcdhing source cells, so it can't be coded like this. Is this what you want your summary sheet to look like?

TestBase.xlsm
	A	B	C	D
1
2		sh1!C9	sh1!H9
3			sh1!H10
4			sh1!H24
5			sh1!H25
6			sh1!H26
7			sh1!H27
8			sh1!H28
9			sh1!H29
10		sh2!C9	sh2!H9
11			sh21!H10
12			sh2!H24
13			sh2!H25
14			sh2!H26
15			sh2!H27
16			sh2!H28
17			sh2!H29
18		sh3!C9	sh3!H9
19			sh3!H10
20			sh3!H24
21			sh3!H25
22			sh3!H26
23			sh3!H27
24			sh3!H28
25			sh3!H29
Sheet1

I want my summery sheet look like

master.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	file 1.xlsx	sh1!C9	sh1!H9	sh1!H10	sh1!H24	sh1!H25	sh1!H26	sh1!H27	sh1!H28	sh1!H29
3	file 2.xlsx	sh2!C9	sh2!H9	sh2!H10	sh2!H24	sh2!H25	sh2!H26	sh2!H27	sh2!H28	sh2!H29
4	file 3.xlsx	sh3!C9	sh3!H9	sh3!H10	sh3!H24	sh3!H25	sh3!H26	sh3!H27	sh3!H28	sh3!H29
5	file 4.xlsx	sh4!C9	sh4!H9	sh4!H10	sh4!H24	sh4!H25	sh4!H26	sh4!H27	sh4!H28	sh4!H29
6	file 5.xlsx	sh5!C9	sh5!H9	sh5!H10	sh5!H24	sh5!H25	sh5!H26	sh5!H27	sh5!H28	sh5!H29
7	file 6.xlsx	sh6!C9	sh6!H9	sh6!H10	sh6!H24	sh6!H25	sh6!H26	sh6!H27	sh6!H28	sh6!H29
8
9
10
11
12
13
14
15
summery

 

[JLGWhiz]

This should do it.

Sub t()
Dim fPath As String, fName As String, sh As Worksheet, wb As Workbook
Set sh = ThisWorkbook.Sheets("Summary")
fPath = ThisWorkbook.Path & "\"
fName = Dir(fPath & "*.xls*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            On Error Resume Next
            With wb.Sheets("list")
                If Err.Number <> 9 Then
                    sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = wb.Sheets("list").Range("C6").Value
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 2).Resize(, 2) = wb.Sheets("list").Application. _
                    Transpose(Range("H9:H10"))
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 4).Resize(, 6) = Application. _
                    Transpose(wb.Sheets("list").Range("H24:H29"))
                End If
                If Err.Number > 0 And Err.Number <> 9 Then
                    MsgBox "Error " & Err.Number & ":  " & Err.Description
                ElseIf Err.Number > 0 Then
                    MsgBox "Sheet 'list' not found in " & wb.Name, vbExclamation, "SHEET NOT FOUND"
                End If
            End With
            On Error GoTo 0
            Err.Clear
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

 

[Mr_A]

This should do it.

Sub t()
Dim fPath As String, fName As String, sh As Worksheet, wb As Workbook
Set sh = ThisWorkbook.Sheets("Summary")
fPath = ThisWorkbook.Path & "\"
fName = Dir(fPath & "*.xls*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            On Error Resume Next
            With wb.Sheets("list")
                If Err.Number <> 9 Then
                    sh.Cells(Rows.Count, 1).End(xlUp)(2) = wb.Name
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = wb.Sheets("list").Range("C6").Value
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 2).Resize(, 2) = wb.Sheets("list").Application. _
                    Transpose(Range("H9:H10"))
                    sh.Cells(Rows.Count, 1).End(xlUp).Offset(, 4).Resize(, 6) = Application. _
                    Transpose(wb.Sheets("list").Range("H24:H29"))
                End If
                If Err.Number > 0 And Err.Number <> 9 Then
                    MsgBox "Error " & Err.Number & ":  " & Err.Description
                ElseIf Err.Number > 0 Then
                    MsgBox "Sheet 'list' not found in " & wb.Name, vbExclamation, "SHEET NOT FOUND"
                End If
            End With
            On Error GoTo 0
            Err.Clear
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

thank you

script working great but can be better if file name in summary sheet linked with file
thanks again

 

[JLGWhiz]

You're welcome,
Regards, JLG