Macro to copy 5 sheets into a new workbook and save it as name in cell?

[tonywatsonhelp]

Hi Everyone,

I need a macro that can take 5 sheets copyinto a new document and then save that document as the name in range D7 and todays date.

the sheets i need are

Data, Totals, Sold, Returned, and Report.

please help if you can

Tony

 

[Fluff]

How about

Sub CopyToNew()
   Dim shts As Variant
   
   shts = Array("Data", "Totals", "Sold", "Returned", "Report")
   Sheets(shts).Copy
   ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheets("[COLOR=#ff0000]Data[/COLOR]").Range("D7").Value & format(Date, "dd-mm-yyy") & ".xlsm", 52
End Sub

Change value in red to suit

 

[tonywatsonhelp]

Brilliant, thank you Fluff

 

[Fluff]

Glad to help & thanks for the feedback