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

Mr_A

New Member
Joined
Feb 21, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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 :)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Which sheet in the master workbook and which cells do you want the copied data pasted to?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
ABCD
1
2sh1!C9sh1!H9
3sh1!H10
4sh1!H24
5sh1!H25
6sh1!H26
7sh1!H27
8sh1!H28
9sh1!H29
10sh2!C9sh2!H9
11sh21!H10
12sh2!H24
13sh2!H25
14sh2!H26
15sh2!H27
16sh2!H28
17sh2!H29
18sh3!C9sh3!H9
19sh3!H10
20sh3!H24
21sh3!H25
22sh3!H26
23sh3!H27
24sh3!H28
25sh3!H29
Sheet1
 
Upvote 0
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
ABCD
1
2sh1!C9sh1!H9
3sh1!H10
4sh1!H24
5sh1!H25
6sh1!H26
7sh1!H27
8sh1!H28
9sh1!H29
10sh2!C9sh2!H9
11sh21!H10
12sh2!H24
13sh2!H25
14sh2!H26
15sh2!H27
16sh2!H28
17sh2!H29
18sh3!C9sh3!H9
19sh3!H10
20sh3!H24
21sh3!H25
22sh3!H26
23sh3!H27
24sh3!H28
25sh3!H29
Sheet1
I want my summery sheet look like
master.xlsx
ABCDEFGHIJK
1
2file 1.xlsxsh1!C9sh1!H9sh1!H10sh1!H24sh1!H25sh1!H26sh1!H27sh1!H28sh1!H29
3file 2.xlsxsh2!C9sh2!H9sh2!H10sh2!H24sh2!H25sh2!H26sh2!H27sh2!H28sh2!H29
4file 3.xlsxsh3!C9sh3!H9sh3!H10sh3!H24sh3!H25sh3!H26sh3!H27sh3!H28sh3!H29
5file 4.xlsxsh4!C9sh4!H9sh4!H10sh4!H24sh4!H25sh4!H26sh4!H27sh4!H28sh4!H29
6file 5.xlsxsh5!C9sh5!H9sh5!H10sh5!H24sh5!H25sh5!H26sh5!H27sh5!H28sh5!H29
7file 6.xlsxsh6!C9sh6!H9sh6!H10sh6!H24sh6!H25sh6!H26sh6!H27sh6!H28sh6!H29
8
9
10
11
12
13
14
15
summery
 
Upvote 0
This should do it.
VBA Code:
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
 
Upvote 0
Solution
This should do it.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top