VBA Copy Sheet - What Am I Doing Wrong

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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)

VBA Code:
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
🤬😡🤧
Figured it out! So frustratingly simple!

I had the following:
VBA Code:
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!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,725
Members
415,924
Latest member
togo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top