Create New Spread From Old Worksheet

[Guest]

I have a spreadsheet with several worksheets, I would like to copy only the
(RIC) worksheet to new spreadsheet. I would like the new spreadsheet to use
same folder location as the old spreadhsheet and the name with the following
format: 'Den' + DDMMYYYYHHMMSS

Please help me complete this task.

Thanks,

 

[Dave Peterson]

Option Explicit
Sub Testme01()
dim ActWkbk as workbook

set actwkbk = activeworkbook

actwkbk.worksheets("Ric").copy 'to a new sheet

with activesheet 'new ric sheet in new workbook
.parent.saveas filename:=actwkbk.path & "\" & "Den" & _
format(now, "ddmmyyyyhhmmss")
.parent.close savechanges:=false
end with
end sub

 

[Dave Peterson]

Minor modifications...

Option Explicit
Sub Testme01()
dim ActWkbk as workbook

set actwkbk = activeworkbook

actwkbk.worksheets("Ric").copy 'to a new sheet in a new workbook

with activesheet 'new ric sheet in new workbook
.parent.saveas filename:=actwkbk.path & "\" & "Den" & _
format(now, "ddmmyyyyhhmmss") & ".xls"
.parent.close savechanges:=false
end with
end sub

I modified the comment on the .copy line and added ".xls" to the workbook name.