Good Afternoon,

I please need your help with automating an otherwise colossal task.

So I have two version of an Excel tool:

- Version 1.4, already populated by our stores resulting in 304 different files
- Version 1.5, blank and recently updated to include several changes from the previous version

I need to change the 304 different workbooks that are 1.4, to version 1.5. They are all saved in the same folder. I have made changes via macro before, but all of them were change something in the existing file, save it, close it, move on to the next in the folder. There were too many changes to the original file to do a change macro, rather than a new workbook. Here is what I'd like to accomplish:

1. Open the blank tool (1.5)
2. Open the first file in a specific folder (1.4)
3. Check if Control!B3 says version 1.4.. if not, save the file to C:\Submissions\WrongVer\ and close it
4. Copy a bunch of cells from 1.4 workbook to 1.5 workbook (i have this macro already recorded)
5. Save 1.5 workbook with it's name based on Structure!J12 in C:\Submissions\Updated\
6. Reopen the blank tool (1.5)
7. Open the 2nd file ..
And so on and so forth until all files in the folder are done.

Sub x()
    Const sPath     As String = "C:\Submissions\"
    Dim sFile       As String
 
    sFile = Dir(sPath & "*.xlsm")
 
    Do While Len(sFile)
        With Workbooks.Open(sPath & sFile)
            On Error Resume Next
            Call Copy_Data
            ActiveWorkbook.Save 
            ActiveWorkbook.Close
        End With
        sFile = Dir()
    Loop
End Sub
Missing from the code above, because I don't know how to do it:
- Open the 1.5 workbook as well
- Check 1.4 for correct version
- Save the populated 1.5 file with cellvalue name

My apologies for asking, I've been trying to make this work all day and clearly my knowledge is not enough to get it done by myself.

I appreciate your time!

Cheers

Maike