Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Loop through closed workbooks, copy range and paste

0

Hi, I have approx 20 workbooks i have to open each week and I would like to have a macro that looks at a file path in a separate book, go to that workbook of the path, copy the range eg ("S213:U319") of each sheet (sheet numbers vary from book to book) paste the range into a different book on single sheet called summary.

The starting row is the same for every sheet but the last row varies.

I have attached a workbook to show what I mean. 

Thank you for any help provided.

excelconsolidate-data
posted by: Wehttam on Thu Mar 28, 2024 at 8:55 pm
Answer
Discuss

Discussion

Hello Wehttam and welcome to the forum,  
Let me see if I understand you correctly. You have 20 workbooks – let’s refer to them as A thru S. Each workbook has multiple sheets – let’s say 6, and refer to them as – 1 thru 6. You want to copy a specific range (S213:U319) of each sheet (1 thur 5) to the Summary sheet (6) of the same workbook.  
1)    What version of Excel are you using?
2)    Are all of the workbooks (A thru S) in the same folder?
3)    Are the worksheets (1 thru 6) of each workbook in the same order? In your uploaded file, the sheets appear to be in a random order.
4)    When the range is pasted to the Summary sheet do you want it to overwrite the existing data or add it below the existing?
5)    You say the last row varies. Is there a cell value/text which can be used to determine what the last row is?  

This info (and maybe more) is needed to be able to offer a solution. Looping thru workbooks and doing a copy/paste is possible, but, without these details we could only provide a generic solution rather than one more specific to your situation.

Cheers   :-)
WillieD24 (rep: 587) Mar 28, '24 at 10:47 pm
Hi WillieD24, Thank you for your prompt response.
I am using Office 365.
No. The workbooks are not in the same folder. They are all in separate folders.
The worksheets are not always in the same order.
With regards to the range being posted I do not want it to overwrite the existing data. I need it to be below.
Column B has values in it that indicate there is data in the row.
I would like the summary sheet to be in a different workbook if that is possible.

Cheers
Wehttam Mar 29, '24 at 12:26 am
Hi Wehttam,
Thanks for providing answers. I work with 2007 and 2016, so I might not be able to provide a viable answer for you. 365 has some features which are not backwards compatible (MS just wants people to pay an annual fee)
What you want to do is now more complicated with files in different locations and the worksheets not being in a consistent order.
Are all files in a different (unique) folder or do some folders contain multiple files? Does each file contain the same number of sheets? Are the sheet names in each file the same? Is the starting point of the range to be copied always the same (S213)? You say there are values in Col-B to indicate there is data, but you don’t say what indicates the last row to be copied.
It will be a couple of days before I can take a deeper look at this; maybe another member can provide a solution quicker.

You may find help in this Nov. 23, 2021 post on this site: 
Copy Specific Cells From Multiple Files
If you, or someone you know, is familiar with VBA, it’s possible you may be able to adapt the code to do the job for you.
WillieD24 (rep: 587) Mar 29, '24 at 9:35 am
Hi WillieD24, I understand what you mean about MS wanting people being charged a fee every year.
Would it help if the summary file was in the same workbook? Each file is different in the number of sheets and the sheet names can be different. The starting point is always the same.
Thank you for your assistance. I will try and get someone that might be able to adapt the code for me.
Wehttam Mar 30, '24 at 2:40 am
Hi Wehttam,
The summary file (worksheet) doesn’t need to be in the same workbook. Will the summary be a summary of a single workbook or will it be a summary of all worksheets from all workbooks? If the summary worksheet is meant to contain only data from one workbook, then it would make sense to be in the same workbook. If the summary is to contain data from all workbooks, then being in a separate file makes more sense. It’s not clear if the summary is for one workbook specific or if it is a summary for all workbooks.
The following question I should have asked near the beginning - where and how is the data currently being copied – to a worksheet within each workbook or a separate workbook?
Here is something to consider. Let’s assume you have 20 workbooks, and each workbook has an average of 5 worksheets of data to be copied. So that means each week you have 100 worksheets of data to be copied. In your example you say rows to be copied are 213 to 319 => 117 rows. (Also, you indicate  there are 3 columns to be copied – S, T, & U, but in the sample file the “Summary” sheet has 12 columns) Let’s assume on average there are 100 rows per sheet. That means 10,000 rows of data per week. If all of the data is going to one worksheet you will only be able to get about 2 years of data (1,040,000 rows) on the sheet. If each workbook has its own summary sheet, then the summary would be able to contain about 40 years worth of data. You just need to decide which way you want to go.
WillieD24 (rep: 587) Mar 30, '24 at 12:33 pm
Wenttam. I agree with Willie that the differing sheet order presents a problem if you want segmented data in your Summary but do all the workbooks contain sheets with variable quantities of Claim numbers related to the (project?) name in the file name (e.g. B308)?  

Also you said above "Column B has values in it that indicate there is data in the row"  so do you need to extract only those rows with  a numerical value in column B?

You say "The starting point is always the same." but where is that?

It's not too tricky to loop through all used rows of all sheets of several workbooks and copy data to the same (or another) workbook but you might have to wait for an answer- Willie and I are just volunteers using our own spare time to help users. In my case, I won't have much spare time for the next 10 days but if you confirm points above I might get chance to provide a working solution.on Monday.
John_Ru (rep: 6377) Mar 30, '24 at 4:15 pm
Hi John_Ru, Thanks for your help. I appreciate your help and I understand being volunteers you have to fit everything in. I think I might have worked some of it out. But to answer your question the starting point in Column B Row 315.
Thank you for your assistance.
Wehttam Apr 1, '24 at 12:29 am
Thanks Wehttam. I thought about it a little yesterday and think I have a full solution more or less worked out. Will try to code and test it today.
John_Ru (rep: 6377) Apr 1, '24 at 3:10 am
Please see my Answer file- hope it does what you want.
John_Ru (rep: 6377) Apr 1, '24 at 10:52 am
@John
As usual, you were able to get an understanding which I couldn't. But Wehttam didn't answer all of my questions. Then there is the discrepency of the original post which says the range to copy is S213:U319; and then in the discussion point of today stating " starting point in Column B Row 315". I had worked out a structure for a macro while waiting for more details but will not pursue further without added details, conflicting details (row 213 vs row 315), and since you have provided a solution.
:-)
WillieD24 (rep: 587) Apr 1, '24 at 11:49 am
@Willie - agreed, I was a bit confused by S213 too. The B315 clue enabled me to (somewhat) guess what was needed from the sample Summary sheet.

@Wehttam - hope I guessed correctly!
John_Ru (rep: 6377) Apr 1, '24 at 12:02 pm
Wehttam - why no comment on my Answer? The lack of response is very discouraging to people like Willie and me who spend hours helping other people for free. Next time you have a question, you may find sadly that no one spares the time to help. 
John_Ru (rep: 6377) Apr 10, '24 at 1:13 pm
Add to Discussion

Answers

0

Wehttam

In the first attached file, you'll find two sheets. The second is "Summary template" - don't change this since it will be used to collect your data from files you list in column A of the first sheet ("Target files") plus the start cell data in column C.

The latter sheet shows the "output" results using files on my PC (where B317.xlsx is just a copy of your B308.xlsx file and there's no B320 file).

Firstly replace the filenames in A with real files and paths (you could have 20 or more).

Then to get an idea of the basic process, use key combination Alt+F11 to display the VB Explorer window. If you don't see a pane tilted "Immediate", press Ctrl+G to display it. Then double click on Module2 to display this macro (with explanatory comments:in red, starting '):

Sub ListFileSheets()

    Dim LstRw As Long, TargLstRw As Long, DestLstRw As Long
    Dim n As Long, FlNm As String, Wb As Workbook, Ws As Worksheet

    'don't update the screen (so file opening is hidden too)
    Application.ScreenUpdating = False

    ' determine last used row in column A
    LstRw = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    'loop down column A from row 2
    For n = 2 To LstRw
        ' check if file exists
        FlNm = Dir(Sheet1.Cells(n, 1).Value)
        If FlNm <> vbNullString Then
            '<< do something >>
            Debug.Print "Found " & FlNm
            Set Wb = Workbooks.Open(Sheet1.Cells(n, 1).Value)
                ' loop through all sheets
                For Each Ws In Wb.Sheets
                    ' << do something only to claim sheets>>
                    If Ws.Name <> "Summary" Then Debug.Print "Found sheet " & Ws.Name & ": Claim No. " & Ws.Cells(6, 6).Value
                Next Ws
                ' close file
                Wb.Close SaveChanges:=False
            Else
            ' say if the file wasn't found
            Debug.Print "Couldn't find " & Sheet1.Cells(n, 1).Value
        End If
    Next n

    Application.ScreenUpdating = True

End Sub

If you click in that and press the Play icon in the menu bar (next to the Pause icon), the macro will (invisibly) open all your files and a list of results will appear in that Immediate window (you can scroll up/down).

Now return to the "Target files" sheet, find the blue button labelled "Create Summary files" near column E, click it and respond "Yes" to the Message Box and after a short while another message will tell you it's done i.e. it has run this much fuller macro from Module1 (with comments in red again):

Sub CollateClaims()

    Dim LstRw As Long, FlLstRw As Long, DestLstRw As Long
    Dim n As Long, m As Long, Strt As String
    Dim FlNm As String, Wb As Workbook, Ws As Worksheet
    Dim Resp As VbMsgBoxResult

    ' determine last used row in column A
    LstRw = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    Resp = MsgBox("Okay to clear output data, search files and overwrite any Summary files with today's date?", vbYesNo, "This may take a few seconds....")
    If Resp <> vbYes Then Exit Sub
    ' clear output columns
    ThisWorkbook.Worksheets("Target files").Range("C2:D" & LstRw).ClearContents
    'don't update the screen (so file opening is hidden too)
    Application.ScreenUpdating = False
    'loop down column A from row 2
    For n = 2 To LstRw
        ' check if file exists
        FlNm = Dir(Sheet1.Cells(n, 1).Value)
        If FlNm <> vbNullString Then
            ' open found workbook
            Set Wb = Workbooks.Open(Sheet1.Cells(n, 1).Value)
            ' make a copy of the summary template
            ThisWorkbook.Worksheets("Summary Template").Copy
            ' loop through all sheets
            For Each Ws In Wb.Sheets
                ' ignore any Summary sheets
                If Ws.Name <> "Summary" Then

                    ' fix start cell for this file (or set B315 if blank)
                    Strt = Sheet1.Cells(n, 2).Value
                    If Strt = "" Then Strt = "B315"
                    ' and extract claim data
                    With Ws.Range(Strt)
                        ' determine last used row in starter column
                        FlLstRw = Ws.Cells(Rows.Count, .Column).End(xlUp).Row
                        For m = .Row To FlLstRw
                            If Ws.Cells(m, .Column) <> "" And _
                                Not (Ws.Cells(m, .Column + 2) = "" Or Ws.Cells(m, .Column + 2) = "-") Then
                                ' copy data
                                Select Case Ws.Cells(m, .Column - 1)
                                    Case "Labour"
                                        ' get last used row in  appropriate output area of Summary
                                        DestLstRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
                                        ' copy range of data and paste values and formats below there
                                        Ws.Range(Ws.Cells(m, .Column + 2), Ws.Cells(m, .Column + 19)).Copy
                                        ActiveSheet.Cells(DestLstRw + 1, "C").PasteSpecial xlPasteValues
                                        ActiveSheet.Cells(DestLstRw + 1, "C").PasteSpecial xlPasteFormats
                                        ' and write project and claim data
                                        ActiveSheet.Cells(DestLstRw + 1, "A") = Left(Wb.Name, 4)
                                        ActiveSheet.Cells(DestLstRw + 1, "B") = "Claim " & Ws.Cells(6, 6)
                                        ' write borders
                                        ActiveSheet.Cells(DestLstRw + 1, "A").Borders.LineStyle = xlContinuous
                                        ActiveSheet.Cells(DestLstRw + 1, "B").Borders.LineStyle = xlContinuous
                                    Case "Material"
                                        DestLstRw = ActiveSheet.Cells(Rows.Count, "V").End(xlUp).Row
                                        Ws.Range(Ws.Cells(m, .Column + 2), Ws.Cells(m, .Column + 14)).Copy
                                        ActiveSheet.Cells(DestLstRw + 1, "X").PasteSpecial xlPasteValues
                                        ActiveSheet.Cells(DestLstRw + 1, "X").PasteSpecial xlPasteFormats
                                        ActiveSheet.Cells(DestLstRw + 1, "V") = Left(Wb.Name, 4)
                                        ActiveSheet.Cells(DestLstRw + 1, "W") = "Claim " & Ws.Cells(6, 6)
                                        ActiveSheet.Cells(DestLstRw + 1, "V").Borders.LineStyle = xlContinuous
                                        ActiveSheet.Cells(DestLstRw + 1, "W").Borders.LineStyle = xlContinuous
                                    Case "Equipment"
                                        DestLstRw = ActiveSheet.Cells(Rows.Count, "AL").End(xlUp).Row
                                        Ws.Range(Ws.Cells(m, .Column + 2), Ws.Cells(m, .Column + 14)).Copy
                                        ActiveSheet.Cells(DestLstRw + 1, "AN").PasteSpecial xlPasteValues
                                        ActiveSheet.Cells(DestLstRw + 1, "AN").PasteSpecial xlPasteFormats
                                        ActiveSheet.Cells(DestLstRw + 1, "AL") = Left(Wb.Name, 4)
                                        ActiveSheet.Cells(DestLstRw + 1, "AM") = "Claim " & Ws.Cells(6, 6)
                                        ActiveSheet.Cells(DestLstRw + 1, "AL").Borders.LineStyle = xlContinuous
                                        ActiveSheet.Cells(DestLstRw + 1, "AM").Borders.LineStyle = xlContinuous
                                    Case Else
                                        DestLstRw = ActiveSheet.Cells(Rows.Count, "BB").End(xlUp).Row
                                        Ws.Range(Ws.Cells(m, .Column + 2), Ws.Cells(m, .Column + 19)).Copy_
                                        ActiveSheet.Cells(DestLstRw + 1, "BD").PasteSpecial xlPasteValues
                                        ActiveSheet.Cells(DestLstRw + 1, "BD").PasteSpecial xlPasteFormats
                                        ActiveSheet.Cells(DestLstRw + 1, "BB") = Left(Wb.Name, 4)
                                        ActiveSheet.Cells(DestLstRw + 1, "BC") = "Claim " & Ws.Cells(6, 6)
                                        ActiveSheet.Cells(DestLstRw + 1, "BB").Borders.LineStyle = xlContinuous
                                        ActiveSheet.Cells(DestLstRw + 1, "BC").Borders.LineStyle = xlContinuous
                                End Select
                            End If
                        Next m

                    End With
                    'Debug.Print Ws.Name
                End If
            Next Ws
            ' save summary in same folder as source, without asking
            Application.DisplayAlerts = False
            FlNm = Wb.Path & "\" & Left(Wb.Name, 4) & " Summary " & Format(Date, "ddmmyyyy") & ".xlsx"
            ActiveWorkbook.SaveAs (FlNm)
            ' close filea
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
            Wb.Close SaveChanges:=False
            ' write output values
            Sheet1.Cells(n, 3).Value = "Saved Summary as " & FlNm
            Sheet1.Cells(n, 4).Value = Now

            Else
            ' say if the file wasn't found
            Sheet1.Cells(n, 3).Value = "Couldn't find " & Sheet1.Cells(n, 1).Value
            Sheet1.Cells(n, 4).Value = "-"
        End If
    Next n

    Application.ScreenUpdating = True
    MsgBox "Done! (See action info in columns C and D)"
End Sub
 

You'll find that it creates Summary files per project (named like B308 Summary 01042024.xlsx where the numeric bit is today's date in ddmmyyyy format) inthe same folder as that in column A.

Note that it relies on there being a number in column B of your files (like B308.xlsx), a category in column A and data in column D on. It will ignore any rows which match that but have "-" or nothing (no name or description) in D.

In the template sheet, there's also an "Uncategorised" section over to the right (in case column A of your source file's data row doesn't match Labour, Material or Equipment).

This then is the file to use weekly (or whatever) to create your Summary files.

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

posted by: John_Ru (rep: 6377) on Mon Apr 1, 2024 at 10:51 am
Discuss

Discussion

Forgot to say that my code assumes that each file name starts with a uniquue 4-digit project number (like B308) and has the same format (but a varying number of sheets) and it will ignore any sheets named "Summary".

You might need to tweak some formatting in the Summary files (and sort or filter data to get things in order).

If you find my file workable but want improvements (e.g. automatic addition of hyperlinks to the resultant Summary files) please ask a new question but refer back to this. 
John_Ru (rep: 6377) Apr 1, '24 at 11:07 am
Wehttam. Please respond to my Answer 
John_Ru (rep: 6377) Apr 3, '24 at 1:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login