VBA Code to Export a specific worksheet from a current workbook and create a new workbook

[jmk15315]

I have a "Root" spreadsheet that I want to have the ability to export a single tab from this current workbook, create a new workbook and name it based on specific cells in the old workbook.
Being self-taught I believe this can be done, but I am at a loss as to how I would accomplish this.

Here is what I have tried, but can't get to work

Sub Button6_Click()

Dim saveAsFilename As Variant

Sheet1.Select
Sheet32.Visible = True
Sheet32.Select
Sheet32.Copy
Windows("G-XXXXX - WORKING FILE.xlsm").Activate
Sheet1.Select
Windows("Book1").Activate

saveAsFilename = Application.GetSaveAsFilename( _
InitialFileName:=Application.DefaultFilePath & "\" & "P" & Sheet2.Range("G11").Value & " - Job Information Form", _
FileFilter:="xls File (*.xls), *.xls", _
Title:="Save")

If saveAsFilename = False Then Exit Sub

End Sub


Any assistance will be greatly appreciated.

 

[Anthony47]

Do you want to save the exported sheet in the same directory of the original workbook, or do you need choosing the destination path?

 

[jmk15315]

Thanks for the reply, The users of the spreadsheet will need to save the exported sheet into specific directories that they choose.

 

[Anthony47]

Your code is basically working... but it misses the SaveAs command; indeed Application.GetSaveAsFilename returns the path and the name, so you need saving the file.
I also would not recommand using the CodeName to refer to your worksheet, in this context: I guess that your "master" workbook is added with new sheets as you go on to replace the one that is moved out to the new workbook, and they will be assigned new codenames; so you should modify the vba code to cope with the new worksheets codename.
Thus:

Dim saveAsFilename As Variant

Sheets("Sheet32").Visible = True    '<<< Use the right sheet Name
Sheets("Sheet32").Copy              '<<< as above
'
saveAsFilename = Application.GetSaveAsFilename( _
InitialFileName:=Application.DefaultFilePath & "\" & "P" & Sheet2.Range("G11").Value & " - Job Information Form", _
FileFilter:="xls File (*.xls), *.xls", _
Title:="Save")

If saveAsFilename = False Then Exit Sub
ActiveWorkbook.SaveAs saveAsFilename    '<<< very necessary
ActiveWorkbook.Close False
ThisWorkbook.Activate                   'Not necessary but...

End Sub

 

[jmk15315]

Thank you for your assistance, Hoping you might be able to help me understand why I get this error when opening the exported sheet.

Should the file be *.xlsm since there is a macro button on the sheet with code assigned? Would there be a way to export the sheet while eliminating the macro button? That would be ideal for the users of the exported file as they would not need access to it.

Again, I can't thank you enough for your support.

 

[Anthony47]

It's quite anachronistic to use a 15-year-old file format; so naming it xlsx or xlsm is a good idea
If you wish to remove the macro then use the xlsx format and add Application.DisplayAlerts = False /True as shown:

saveAsFilename = Application.GetSaveAsFilename( _
InitialFileName:=Application.DefaultFilePath & "\" & "P" & Sheet2.Range("G11").Value & " - Job Information Form", _
FileFilter:="xls File (*.xlsx), *.xlsx", _
Title:="Save")

If saveAsFilename = False Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs saveAsFilename, 51    
ActiveWorkbook.Close False
Application.DisplayAlerts = True
ThisWorkbook.Activate                  

End Sub

 

[jmk15315]

I apologize for the delay in getting back to you. Thank you for the fix, that solved my error issue.

I do have a follow-up question. On the sheet I am exporting, I have three "Commandbuttons" and two "Optionbuttons". Is there a way to have these either deleted or the macros disabled after they are exported? Once I export the file, the intent would be for the newly created worksheet to be a standalone document not linking back to the "Parent" document

 

[Anthony47]

Did you try saving the file in the xlsx format, that is without macro? That should remove the macro from the saved file.
If you wish to remove also the controls, then I think this addition should work:

For Each Shp In ActiveSheet.Shapes
    Debug.Print Shp.Type, Shp.Name
    If Shp.Type = 8 Or Shp.Type = 12 Then
        Shp.Delete
    End If
Next Shp

Insert it just after Sheets("Sheet32").Copy and before saveAsFilename = Application.etc etc

 

[jmk15315]

I did make the change to .xlsx and that resolved my error, but it still left the link to the parent document and allows it ti run the code which opens up the "parent" doc.

In the new code you have suggested, can you help me understand the commands?

For Each Shp In ActiveSheet.Shapes (What is Shp?) (My assumption is Shp=Shape) Receive a Compile Error: Variable not defined
Debug.Print Shp.Type, Shp.Name
If Shp.Type = 8 Or Shp.Type = 12 Then
Shp.Delete
End If
Next Shp

 

[Anthony47]

If your vba module is set to Option Explicit then you must declare any variable used.
So add a Dim Shp as Shape to your initial declarations (but Dim Shp would also be sufficient)