Opening files from a range list in Excel, copy, paste into main file then close, repeat

[Bubbachew]

Hello and a very happy and prosperous new year to everyone!

My first post hopefully will make some sense but here goes -

I have a main file that needs to combine in data from other files.

The list of files change overtime as new divisions/departments are set up and/or closed.

I keep the upto date list of current files in a Named Range in Excel called 'FileNames'.

The current files are all stored in a folder that changes with the month, I keep this folder reference also in Excel Range as a single cell called 'Location'.

Currently I have a macro that I just recorded and then updated for the filenames and location as it moves and changes over time. not very efficient.

I tried to recreate with Dim and Set a For Next loop, but did not work at all, this is my attempt followed by an example of what I am trying to loop.

any insight would be very much appreciated!

' Dim Location As String

' Dim FileName As Variant

' Location = "G:\Finance\Year 2018\ReturnsDoNotUseTest"

' FileName = Range("Filenames").value

' Workbooks.Open FileName:= Location & Filename _
, UpdateLinks:=0

The actual code I want to loop -

Application.Calculation = xlManual
Sheets("Summary data").Select
Cells.Select
Selection.Delete Shift:=xlUp

Workbooks.Open FileName:= _
"G:\Finance\Year 2018\ReturnsDoNotUseTest\100 - Business Support 2018 Budget FY.xlsx" _
, UpdateLinks:=0

Sheets("2018 Budget Database").Select

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Selection.Copy
Windows("BudgetCombination2018.xlsm").Activate
Sheets("Summary data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("100 - Business Support 2018 Budget FY.xlsx").Activate
ActiveCell.Copy
ActiveWindow.Close

 

[Fazza]

hello. this may help. regards

Sub Bubbachew()


    Dim i As Long
    
    Dim sLocation As String
    Dim sOneFileName As String
    
    Dim aAllFileNames As Variant
    
    sLocation = Range("Location").Value2 '"G:\Finance\Year 2018\ReturnsDoNotUseTest"
    If Right$(sLocation, 1) <> Application.PathSeparator Then sLocation = sLocation & Application.PathSeparator
    
    aAllFileNames = Range("Filenames").Value2


    For i = LBound(aAllFileNames, 1) To UBound(aAllFileNames, 1)
        Debug.Print "From this loop call your routine to process file " & sLocation & aAllFileNames(i, 1)
    Next i
    Erase aAllFileNames
    
End Sub