VBA Copy Sheet - What Am I Doing Wrong

[t0ny84]

Hi,
I am trying to copy a sheet to another workbook but am having major issues, I feel like it should be a simple thing but I have spent far too long on this!

Purpose of code when button pushed:
1) Check if Backup folder exists - if not it will create.
2) If the folder exists then it will check if the file (year.xlsx) is in the directory. - If not it will create it.
3) If the file is there it will add a copy of the ThisWorkbook.ActiveSheet into year.xlsx at the end of the sheets and then save it.
4) Rename the sheet based on what the person enters into the Input Box. (MONTH)

Sub BackUpSheet()

' Used to back up sheets
'To use this you must set a reference for Scripting Runtime
'--------------------------------------------------
'1.  In the VBE window, Choose Tools | References
'2.   Check the box for Microsoft Scripting Runtime
'--------------------------------------------------

Dim BackupFolderPath As String
Dim BackupFolderPathExists  As String
Dim FName As String
Dim NewBook As Workbook

Dim fso As FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

BackupFolderPath = "FOLDER LOCATION"
BackupFolderPathExists = Dir(BackupFolderPath, vbDirectory)
BackupFileExists = Dir(BackupFolderPath & "\" & FName)
FName = Year(Now) & "___.xlsx"

If BackupFolderPathExists = "" Then ' Checks if backup folder exists.
fso.CreateFolder (BackupFolderPath) 'it it doesn't exist it this creates it.
End If

If fso.FolderExists(BackupFolderPath) Then 'Checks if backup folder exists.
If BackupFileExists = "" Then 'Checks if backup file exists.

'File Doesn't Exist
' Creates New Workbook and names
InputBox "Enter Month"
Set NewBook = Workbooks.Add

ThisWorkbook.ActiveSheet.Copy After:=NewBook.Sheets(Sheets.Count)
With NewBook
.SaveAs Filename:=BackupFolderPath & "\" & FName
.Close
End With

Else
' File Exists
InputBox "Enter Month"
Thisworkbook.activesheet.copy After:=fname.Sheets(Sheets.count)
fname.sheets.name = monthq
.Save
.Close
End If
End If
End Sub

Thanks in advance,
t0ny84

 

[t0ny84]

???
Figured it out! So frustratingly simple!

I had the following:

BackupFileExists = Dir(BackupFolderPath & "\" & FName)
FName = Year(Now) & "___.xlsx"

This code meant that when it was adding the Fname to the BackUpFileExists Variable - Fname was nil so it wouldn't work. To fix I swapped positions and BOOM!