Simplifying VBA Script

[candrews84]

I am trying to rewrite the following script to increase efficiency and make it easier to enter a list of files that need to be opened. The whole process utilizes the following:

-Compile File (This is where all the data will be pasted from various source files). All pastes in the compile file will happen in cells A, B, and C, starting with A1,B1,C1, then the next paste will occur in A2,B2,C2, etc.

-Source Files (These are the files that are opened and data is copied from to be pasted into the 'Compile File', data that is copied from the source file is in non-concurrent cells, however, the data from each source file will be in the same non-concurrent cells. For each source file data will be copied from cells U16, U6, and C61)

Below is the code I currently have which opens the source file, copies from cells U16, U6, and C61, and pastes the data into the Compile File in cells A1, B1, and C1 respectively:

Workbooks.Open Filename:= _
"X:\Reports\Analysis\2016\Source File XXXX\Source File XXXX.xlsx"
Range("U16").Select
Selection.Copy
Windows("Compile Filexlsx").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Source File XXXX.xlsx").Activate
Range("U6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Compile File.xlsx").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Source File XXXX.xlsx").Activate
ActiveWindow.SmallScroll Down:=18
Range("C61").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Compile File.xlsx").Activate
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Source File XXXX.xlsx").Activate
ActiveWindow.Close
End Sub

My goal, if possible, is to simplify the script where I can add the source file paths within the code successively as all the file paths have some numeric variation in folder and file name. The compile file is in a static location so the code would ideally read through the source file locations and names, open each file, copy the relevant data (cells U6, U16, and C61) then paste into the compile file (cells A1, B1, and C1), then move to the next source file repeat the process and paste the data in the compile file the next row down (cells A2, B2, C2).

Any suggestions on simplifying the code?

 

[igold]

Does this do anything for you...

Sub test()


    Dim src As Variant
    
    Workbooks.Open Filename:= _
    "X:\Reports\Analysis\2016\Source File XXXX\Source File XXXX.xlsx"
    src = Array(Range("U16").Value, Range("C61").Value, Range("U6").Value)
    Windows("Compile File.xlsx").Activate
    Range("A1:C1") = src
    Windows("Source File XXXX.xlsx").Close
    
End Sub

igold