VBA keeps closing BOTH workbooks (only 1 specified)

[jamesha777]

Hi all - any help would be appreciated.

I have VBA that loops through a tab on a 'master sheet', changing the club on the tab, and saves down as a separate file for that club. It repeats this for each club, saving the sheet and closing it down each time to return to the 'master sheet'.

I have two master sheets that perform different functions, but all in all, just have different formula. If I change the vba to reference this other master sheet, after saving the first club's file down, it closes all Excel sheets open, and so obviously breaks the code.

Does anyone have any answers? I've tried using both Activeworkbook.Close & Workbooks("").Close to specify, but both end up closing excel entirely.
It's really odd as the VBA works when referencing he first master sheet.

Sub CreateFiles()
'
    Dim mySheetName As String
    Dim numberClubs As Integer
    Dim myClubName As String
    Dim myClubCode As String
    Dim mytab As String
    Dim fileLocation As String
    
    'Sets file location to save club down later
    fileLocation = Sheets("LookUps").Range("I2")
    
    'sets which master sheet to use for looping clubs
    mytab = Sheets("LookUps").Range("G2")
    
    'How many clubs to loop through
    numberClubs = Sheets("Lookups").Range("E2")
    
    
    'Loop clubs through master sheets and save as individual files
    For clubCounter = 2 To numberClubs + 1
    
    
        'store club code & club name as string
        myClubName = Sheets("LookUps").Range("B" & clubCounter)
        myClubCode = "'" & Sheets("LookUps").Range("a" & clubCounter).Text


        'enter the club code in the master sheet
        Sheets("2020CombinedTarget").Range("C3") = myClubCode
        
    
    'copy as a new sheet and save as values
    Sheets(mytab).Select
    Sheets(mytab).Copy after:=Sheets(mytab)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    'Change sheet name to club name
    mySheetName = "T" & Right(myClubCode, 3)
    ActiveSheet.Name = mySheetName


    'Save down file
    Sheets(mySheetName).Move
    ActiveWorkbook.SaveAs Filename:= _
        fileLocation & mySheetName & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

'I suspect this is the problem area?????
    Workbooks(mySheetName & ".xlsx").Close


    Next
    
End Sub

 

[jamesha777]

From further research, it looks as if 'Workbooks (").Close can cause glitches in Excel, closing all open workbooks. Does anyone have a work around for this? I'm desperate now for work!