Macro: save workbook as, then clear contents

[gerrardj]

Hi,
I have a workbook which I use as a template. I.e. I fill certain ranges with data, then save that workbook under a different name, and clear the contents from specified ranges of cells, so that I can fill the "new" workbook with new data, keeping the old one filled as is, under its original name and in its original location.
I want to put that pretty cumbersome saving and then manually clearing into a macro: Save active workbook under new name, and once saved under a new name, clear contents from specific cell ranges in the new workbook.

I've found this piece of code to open a save dialogue box

Application.Dialogs(xlDialogSaveAs).Show

and this piece of code to clear contents of a specified cell range

Sheets("Sheet1").Range("c9:c28").ClearContents

.
However, I can't manage to compile this into a working sequence. If I just put them into one module like this, save as code first, then the clear content code, it will also clear the contents from the original workbook, instead of only clearing contents in the newly saved workbook after saving it.

Can anybody help me?
Thanks
gerrard

 

[EXCEL MAX]

Hello Gerradj, welcome to this forum.
Do the trick... remember the old workbook name and save it again.
Try this...

Sub SaveDuplicate()
   
    Dim vWBOld As String
   
    vWBOld = ActiveWorkbook.FullName
    Application.Dialogs(xlDialogSaveAs).Show
    Sheets("Sheet1").Range("C9:C28").ClearContents
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (vWBOld)

End Sub

 

[gerrardj]

Hello Gerradj, welcome to this forum.
Do the trick... remember the old workbook name and save it again.
Try this...

Sub SaveDuplicate()
  
    Dim vWBOld As String
  
    vWBOld = ActiveWorkbook.FullName
    Application.Dialogs(xlDialogSaveAs).Show
    Sheets("Sheet1").Range("C9:C28").ClearContents
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (vWBOld)

End Sub

Thanks EXCEL MAX

that's almost it. Just tested, and what this code does is save the workbook under a new name, but clear the contents in the original workbook, while the newly saved workbook remains as is. I would like to keep the contents in the original workbook, and clear the contents in the newly saved workbook.

 

[EXCEL MAX]

Then use this code.

Sub SaveDuplicate()
   
    Dim vWBOld As String, vWBNew As String
    
    vWBOld = ActiveWorkbook.FullName
    Application.Dialogs(xlDialogSaveAs).Show
    vWBNew = ActiveWorkbook.Name
    
'change basic template
    Sheets("Sheet1").Range("C9:C28").ClearContents
    
    ActiveWorkbook.Save
    Workbooks.Open (vWBOld)
    Workbooks(vWBNew).Close
    
End Sub

 

[gerrardj]

Thanks so much! That worked fine.

 

[EXCEL MAX]

I'm glad if this was helpful.