Copy Range to a New WorkBook + Name Sheet a cell Value + Name WorkBook another Celll Value

[Corey]

I have a spreadsheet that has many formulas in it.
I want to COPY the format(Cell Shading + Font Size/Colour + Column & Row
Dimensions) and the Cell Values from a specific Sheet Range of "A1:U41",
and
Place this copied range into a NEW WorkBook, bringing all the above
mentioned formats with it.

Then name that worksheet cell value "K2",
then

Name and save the New WorkBook the cell value "E2".

I have tried a Macro Recorder, and all started well but the paste step that
recorded comes up with an error.
The error was at the line below with arrow:


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SaveTS()

Sheets("F Flintstone").Select
Range("A1:U41").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste ' <==================================== ERROR
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D,G:G,J:J,M:M,P,S:S").Select ' <==== Formats the Column
widths as the copied sheet
Range("K2").Activate
Selection.ColumnWidth = 1
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "F Flintstone" ' <======== Names the New
WorkBook Sheet the Cell Value
Range("A1").Select ' <===================== Need to add WorkBook Naming
from Cell Value "E2" here Also
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Any idea's why, and how to get around?

Regards

CTM

 

[Corey]

OK,
Got the date in a new wkbook now from code below:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Save2()
With ActiveWorkbook.Sheets("F Flintstone")
.Select
.Range("a1:u41").Copy
Workbooks.Add
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D,G:G,J:J,M:M,P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet1").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet1").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Now for the Naming of the sheet

 

[Corey]

Ok,
Need to name the WorkBook Todays Date now.

I have a date format cell i am trying to call the workbook, but because it
contains "/"'s i get an error(Although i have formatted the cell to view 31
October 2006).
How can i get the date in the cell to remove the "/"'s? OR Use the Cell View
Format instead.
Thus avoiding the error.