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!

 

[tweedle]

Can try this approach...
The .Copy (omitting the target) will create the new workbook for you
Avoids dealing with the 'extra' Sheet1 as well.

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

 

[jdev33]

Tweedle,

I started with trying the .copy route, but my concern with it was that it was copying all of my macros over. The only solution I could really think was to add a new workbook first and then copy the sheets over.

 

[tweedle]

It's still a sheet.copy as you had before, not a workbook.copy

 

[jdev33]

Ok; I've looked at it a little further. It is not actually copying the macros; it does, however, do this: if I open the new workbook with the original still open then it does not prompt me for anything. If, on the other hand, I close the original workbook and then open the new one it prompts a security warning to enable automatic updating of links.

 

[tweedle]

We can break the links before we save the file :

Sub NewReport()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    Dim srcbook As Workbook
    Set srcbook = ThisWorkbook
    Dim varstrLinks As Variant        '  Array for Links to break
    
    MyDate = Date
  
    Dim dateStr As String
    dateStr = Format(MyDate, "MM-DD-YY")
    
    srcbook.Sheets(Array("Sheet1", "Sheet2")).Copy
    Set W = Workbooks(Workbooks.Count)
    [COLOR=blue]'Break the links
[/COLOR]    'Only breaking xl Links [OLE Links would be handled seperately]
    varstrLinks = W.LinkSources(Type:=xlLinkType[COLOR=blue]ExcelLinks[/COLOR]) 'Set the links array
    For LinkIdx = LBound(varstrLinks) To UBound(varstrLinks)
        W.BreakLink Name:=varstrLinks(LinkIdx), Type:=xlLinkTypeExcelLinks
    Next
    W.SaveAs Filename:="H:\PAR\" & "New Report Name" & " " & dateStr, FileFormat:=51
    W.Close False
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    ActiveWorkbook.Close True
End Sub

 

[jdev33]

You've lost me on the whole links thing. Can you possible give an example?

 

[jdev33]

Just to update: I've got some of this solved but it's creating another problem for me. I get an error message saying that excel can't copy the data because the destination doesn't have the same number of rows and columns. I forget the error number. But if you have any advice on that then it would be much appreicated. Thanks.

 

[tweedle]

When you copy a sheet (say Sheet1) off a workbook, and that sheet has formula-references, and those formula references look to another sheet (say Sheet2) in the original workbook, the copy of Sheet1 will carry those Sheet2 references with it [as Links]. To suppress the message you see for updating links; they can be broken and made static values. The same thing that was done in VBA can be done in Menu Option: Data/Edit Links.

I'm struggling to think of a tangible simile. Cloning/Mitosis maybe.

 

[tweedle]

Are we still dealing with xlsx (type 51) files?