Cannot make this script work!!!!!

iRobert

New Member
Joined
May 15, 2019
Messages
14
Good Saturday to everyone!
I have been trying to tweak this script for days, but I am no expert.
This script automatically kicks in every time the main Excel file is closed.
It should:
1 - Export a sheet called "Master" from the closing Excel file by creating a new one called Master(Progressive Number).xlsx
2 - Master should be exported into a subdirectory called "\Masters\.
3 - If directory does not exist it should be created.
4 - The name of the new file Master should have a prorssive number so that files keep adding like "Master1.xlsx", 'Master2.xlsx" an so on.

The script below does create the right "Masters" directory, but it creates only one file called "1.xlsx" and nothing else.
If the directory Masters is created the script does nothing else.

Can anyone help me?

Thanks
iR
Code:
Sub ExportMaster()
    Dim strFolder As String
    Dim strFile As String
    Dim m As Long
    Dim n As Long
    Dim wb As Workbook
    strFolder = ActiveWorkbook.Path & "\Masters\"
    strFile = Dir(strFolder & "Master" & "*.xlsx")
    If Dir(strFolder, vbDirectory) = "" Then
            MkDir strFolder
        End If
    Do While strFile <> ""
        m = Val(Mid(strFile, 7))
        If m > n Then
            n = m
        End If
        strFile = Dir
    Loop
    n = n + 1
    Worksheets("Master").Copy
    Set wb = ActiveWorkbook
    wb.SaveAs strFolder & n & ".xlsx"
    wb.Close
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,108,771
Messages
5,524,795
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top