VBA keeps closing BOTH workbooks (only 1 specified)

jamesha777

New Member
Joined
Jul 15, 2019
Messages
2
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.
Code:
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
 
Last edited by a moderator:

jamesha777

New Member
Joined
Jul 15, 2019
Messages
2
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!
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top