Question on copy/paste while retaining cell format

[jameslytle]

I have tried this multiple ways until I am blue in the face and have not been able to solve it. I am trying to copy one worksheet and paste it into a new one. What I am doing is have a template worksheet that is used to create duplicate worksheets (as needed) that can be modified while retaining the template with no modifications.

Below is what I have so far. The lines that are remarked out is where I start to run into issues.

***************************
Sheets.Add.Name = "CharData_" & Me.CharLastText.Text
'Sheets(CharData_Template).Copy
'Sheets("CharData_" & Me.CharLastText.Text).Paste
Sheets.Add.Name = "CharSheet_" & Me.CharLastText.Text
'Sheets("CharSheet_Template").UsedRange.Copy
'Sheets("CharSheet_" & Me.CharLastText.Text).Paste
Sheets("CharData_" & Me.CharLastText.Text).Range("B2") = Me.CharFirstText.Text
Sheets("CharData_" & Me.CharLastText.Text).Range("B3") = Me.CharLastText.Text
Sheets("CharData_" & Me.CharLastText.Text).Range("B4") = Me.PlayFirstText.Text
Sheets("CharData_" & Me.CharLastText.Text).Range("B5") = Me.PlayLastText.Text
*********************************
What I am doing is using a UserForm to capture data and then creating the new worksheets from the templates to be able to place the data obtained from the UserForm. These templates have some formatting (i.e. cell size, Merged cells and others) that I need to keep in tact after the new worksheet is created.

Everytime I put in the remarked lines I get an error code and it will not copy the data. The one time I got it to copy the data to the new worksheet it did not retain the formatting from the cells. Additional information; I have tried the command "Paste =:xlpasteformat (or pasteall) and it still did not work.

Any help is appreciated.

Jim

 

[footoo]

Sheets(CharData_Template).Cells.Copy Sheets("CharData_" & Me.CharLastText.Text).Cells

 

[jameslytle]

Thanks for the response.

I actually just found out how to do it via a different method.

*************************
'Create new Character Data WorkSheet and copy template data to it.
Sheets("CharData_Template").Copy before:=Sheets("CharData_Template")
ActiveSheet.Name = "CharData_" & Me.CharLastText.Text
'Create new Character Sheet WorkSheet and copy template data to it.
Sheets("CharSheet_Template").Copy before:=Sheets("CharData_Template")
ActiveSheet.Name = "CharSheet_" & Me.CharLastText.Text
*************************

Once again. Thanks for the quick response.

Jim