Trouble with vba for creating new workbook then copying over from original

[jdev33]

The problem in question centers around one workbook which contains all of my data and breakdowns spread across a ton of worksheets. I'm trying to get macros set up to copy select sheets to a new workbook. I think my biggest problem is getting the coding right for the destination workbook since the name includes a date string that changes each day. The code that I've got so far to just create the new workbook and close it is:

Sub NewReport()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    MyDate = Date
  
    Dim dateStr As String
    dateStr = Format(MyDate, "MM-DD-YY")
  
    Set W = Application.Workbooks.Add
  
    W.SaveAs Filename:="N:\PAR\" & "New Report Name" & " " & dateStr, FileFormat:=51
        
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    ActiveWorkbook.Close True
End Sub

This works and does what I want in regards to creating the new document, naming it the way it should be named, and at the end closing it. What I need help with is that middle portion for copying specific sheets from the original workbook to this new one. What I was thinking was along the lines of:

    With Workbooks("Original Workbook.xlsm")
            .Sheets(Array("Sheet1", "Sheet2")).Copy_ Before:=Workbooks("destination.xls").Sheet1

Or at least some type of array to get exactly what I want to copy over. The biggest sticking point is getting the destination workbook path name correct. Any advice regarding individual pieces of this little project or on the whole is greatly appreciated. Thanks!

 

[jdev33]

I still need to save it as an .xlsx. As far as values and links go the only references I have are on the summary page, which does reference to individual sheets; however, all sheets referenced in the summary page are included in the document I'm trying to create. Been busy with other things so I haven't been able to sit down with this the past couple of days.