To save file copy - after opening Macro Enabled Excel Worksheet

[Elena Margulis]

Hello,
I have macro enabled Ms Excel Worksheet (data entry Form) with various procedures and data in it.
I have one part of the vba code in it, where it should create a backup copy of the sheet - right after opening it:

Private Sub Workbook_Open()

  Dim wb As Workbook, shA As Worksheet, shC As Worksheet
  Dim sh As Worksheet, i As Long, strBackup As String, arr As Variant

  Set shC = ThisWorkbook.ActiveSheet
  strBackup = Range(ThisWorkbook.Names("FolderPathBackup")).Value
  Set wb = Workbooks.Add
   shC.Copy before:=wb.Worksheets(1)
   Set shA = wb.Worksheets(1)
   If wb.Worksheets.Count > 1 Then
        'delete all sheets, except the first
        For i = wb.Worksheets.Count To 2 Step -1
          Application.DisplayAlerts = False
            wb.Worksheets(i).Delete
          Application.DisplayAlerts = False
        Next i
   End If
   arr = Split(strBackup, ".")
   arr(UBound(arr)) = "xlsx"
   strBackup = Join(arr, ".")
   wb.SaveAs strBackup, xlWorkbookDefault
   wb.Close False

This code should create a Worksheet copy inside of created [Backup Data] folder.
The folder path is stored in a [Config] Sheet, in front of a [FolderPathBackup] cell: (I highlighted in blue)

The problem - for some reason, it only works (creates a backup copy of the Worksheet) if I'd created this folder on E:\Projects\Client Satisfaction Survey
But , if I want to move my backup file to E:\BackupData - no copy is created (empty folder as a result).

I am not proficient in vba - and don't understand - where else is this path (E:\Projects\Client Satisfaction Survey) still exists in this vba code?
Or - what should I update in the code, in order to be able to just modify A4 cell (FolderPathBackup reference) so that the copy would be saved on E:\BackupData (or anywhere I write in front of FolderPathBackup)

Thank you for any help, suggestions!

 

[JoeMo]

In what worksheet and cell is the named range: "FolderPathBackup"?
Your worksheet image shows a mis-spelling of Backup in cell B3 (its Bakup), is that the way it is spelled on the actual sheet? If yes, is it spelled differently on the folder title?
You may be missing a path separator in the variable strBackup. What is the value of this variable at the line where arr is created?

 

[Elena Margulis]

In what worksheet and cell is the named range: "FolderPathBackup"?
Your worksheet image shows a mis-spelling of Backup in cell B3 (its Bakup), is that the way it is spelled on the actual sheet? If yes, is it spelled differently on the folder title?
You may be missing a path separator in the variable strBackup. What is the value of this variable at the line where arr is created?

Thank you, you were correct - my folder name was BackupData, while it said in my Config sheet of the Form - BakupData
It's working now !!!

ps-
One thing I still don't understand:
my code is only working after when I changed its 4th line

from:
strBackup = Range(ThisWorkbook.Names("FolderPathBackup")).Value

to:
strBackup = Range(ThisWorkbook.Names("BackupPath")).Value

I don't understand - what is "BackupPath"?
I thought I should reference A3 cell name inside that line?

 

[JoeMo]

Thank you, you were correct - my folder name was BackupData, while it said in my Config sheet of the Form - BakupData
It's working now !!!

ps-
One thing I still don't understand:
my code is only working after when I changed its 4th line

from:
strBackup = Range(ThisWorkbook.Names("FolderPathBackup")).Value

to:
strBackup = Range(ThisWorkbook.Names("BackupPath")).Value

I don't understand - what is "BackupPath"?
I thought I should reference A3 cell name inside that line?

You are welcome.
FolderPathBackup is a named range, as is BackupPath. You can go to Formulas>Name Manager to see what worksheet(s) and cell(s) those names refer to. Then look at the cell contents to compare the two.

 

[Elena Margulis]

You are welcome.
FolderPathBackup is a named range, as is BackupPath. You can go to Formulas>Name Manager to see what worksheet(s) and cell(s) those names refer to. Then look at the cell contents to compare the two.

Thank you so much! Makes sense completely now!