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
and this piece of code to clear contents of a specified cell range
.
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
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
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