Looping through creating workbooks and saving them based on a range

[MrJamesS]

Hi,

Am a basic VBA user, so forgive me for blatent errors

I am trying to do the following:

- "control" sheet shows filepath location (d5) as well as a range of names (g5:g7)
- in filepath, if folder "Club Data Files" does not exist, create it
- in new folder, create workbook and save it based on the 1st cell in a range, and close workbook
- loop through creating a workbook based on the next cell in the range, save, close, and so on

I have reduced the range for ease of testing. I am getting an error when it's trying to save the new workbook, possibly because of how it's interpreting the cell/clubname? Trial and error isn't working

Sub Test()
'
' Test Macro
'
' Go to Control sheet
    Sheets("Control").Select

' If club data folder does not exist, create it. If it does, continue

Dim Filelocation As String
Filelocation = Range("d5")

Dim fdObj As Object
    Application.ScreenUpdating = False
    Set fdObj = CreateObject("Scripting.FileSystemObject")
    If fdObj.FolderExists(Filelocation & "Club Data Files") Then
    Else
        fdObj.CreateFolder (Filelocation & "Club Data Files")
    End If
    
' Calculate
    Calculate
    
' Loop through clubs - set club range
    Dim clubs As Range
    Dim cell As Range
    Dim clubname As String
    Set clubs = Sheets("Control").Range("g5:g7")
    For Each cell In clubs
    clubname = cell.Value
    
' Create new file and save as club name
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=Filelocation & "Club Data Files\" & clubname & ".xlsx"
    Active.Workbook.Close

'Loop through
    Next cell
    Application.ScreenUpdating = True


End Sub

 

[MrJamesS]

Sorry, just to add:

I've added Active.Workbook.Close for ease of asking the question above. Actually the macro continues past this and I need to reference this cell/file name in numerous places. e.g. If the cell in G5 is called "ABC", I need to filter by "ABC" in another part of the file. However I've unsure on how to reference it. Likewise I'll need to say Windows("ABC").Activate, but again not sure if this will work if my original query doesn't work.

Thanks

 

[MrJamesS]

Stupidly, I had a file open with the same name as the first cell in the range....

Works now