VBA save copy of worksheet in same workbook as cell name with button

[flashgordie]

I expect this may be a lot to ask, however I am hoping someone may be able to help me.

I have a workbook called “financials” that contains a worksheet called “template”. In the template worksheet, cell F3 contains a date.

I would like to add a button to the template worksheet. This button, when clicked by the user, would create a copy “template worksheet” and give the new copy a name of the contents found in cell F3. At the same time, it would place the newly created “F3 worksheet”, in the second position, or immediately to the right of the “template worksheet”.

Although this is not necessary, it would be a bonus, if when the new worksheet is created, if it could populate cell A3 in the copied worksheet with the text “Form Completed” and protect this newly created worksheet from being edited.

The code would need some form of validation so that if there was already a worksheet with the name of “F3”, that the user be asked to confirm over writing the “F3 worksheet”.

Note: this all would happen within the same workbook as financials. I do not want to create, or save to a new workbook.

Any help would be appreciated.

 

[jamtay317]

what are the cell refrances that you want coppied over? what is some of the data?

 

[flashgordie]

Hi Jamtay317, Thanks for the reply. I'm not really looking to copy any specific cell references or data, I would like to save a copy of the entire workSHEET. I am wanting the user to click a button located on the "template workSHEET" that will copy the entire "template workSHEET", and save a copy, naming the copy with the contents of cell F3 in "template workSHEET" in the same "financials workBOOK".

 

[jamtay317]

ok, you tell me the range that you want coppyied to a new work sheet? being that you really dont want to copy all 1 billion cells or what ever there is

 

[flashgordie]

Hi again. Okay, I see what you you are saying, I would not have considered that. I suppose... if it is not the entire worksheet it would be A1:x40

 

[jamtay317]

thanks, I'll work on it and try to get it to work

 

[flashgordie]

Hi jamtay, I realize this is very rudimentary, but this is something that I recorded. I am hoping to manipulate to get the results I am hoping for.

Sub copysheet()
'
' copysheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(2)
Sheets("Template (2)").Select
Sheets("Template (2)").Move Before:=Sheets(2)
Sheets("Template").Select
End Sub

I will need to add the cell reference (F3) to pull the new name, and add some type of validation for overwriting sheets with the same name. The more I have played with this I would like to protect the new sheets from being edited. and then incorporate the button that will run this macro.