I am working in a worksheet that has a lot of tables and query’s and I want to make a Macro to save the document that has been filled in and start a blank one with the same query’s and tables but only empty.

I got the clearing off the relevant tables code correct and the adding of one to the year that is set to a range in my workbook

I have the following code for the save as function

Sub SaveAs()

ChDir "C:\User\Username\Desktop\File Location"
AcriveWorkbook.SaveAs Filename:= _

"C:\User\Username\File Location\My filename" &  Sheets("MySheet").Range("F2") & ".xlsm" _

, FileFormat:=52, CreateBackup:=False

ActiveWorkbook.Worksheets("MySheet).Unprotect Password:=" My Password"

Sheets("MySheet"). Range("F2") =Range("F2") +1

ActiveWorkbook.Worksheets("MySheet").Protect Password = "My Password" 

Dim TB As ListObject

    For Each TB In ThisWorkbook.Worksheets("MySheet").ListObjects

        TB.DataBodyRange.ClearContents

    Next TB

ChDir "C:\User\Username\Desktop\File Location" AcriveWorkbook.SaveAs Filename:= _

"C:\User\Username\File Location\My filename" &  Sheets("MySheet").Range("F2") & ".xlsm" _

, FileFormat:=52, CreateBackup:=False

End Sub

I tried changing the save location the range and the sheet.

What I want to happen is to save 2 files the old one and then the new one. But when I run the code Individually the +1 code and ClearContnts code work.

The save as code doesn’t save 2 documents it just overrides my existing Workbook and saves it. So all my old data entry is erased and I can’t get it back.

How can I solve this problem?