Macro: save workbook as, then clear contents

gerrardj

New Member
Joined
May 14, 2014
Messages
5
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
VBA Code:
Application.Dialogs(xlDialogSaveAs).Show
and this piece of code to clear contents of a specified cell range
VBA Code:
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 Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Hello Gerradj, welcome to this forum.
Do the trick... remember the old workbook name and save it again.
Try this...
VBA Code:
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

New Member
Joined
May 14, 2014
Messages
5
Hello Gerradj, welcome to this forum.
Do the trick... remember the old workbook name and save it again.
Try this...
VBA Code:
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

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Then use this code.
VBA 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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,436
Messages
5,636,282
Members
416,910
Latest member
zezspecs

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