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

jmk15315

Board Regular
Joined
Nov 7, 2021
Messages
73
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Do you want to save the exported sheet in the same directory of the original workbook, or do you need choosing the destination path?
 
Upvote 0
Thanks for the reply, The users of the spreadsheet will need to save the exported sheet into specific directories that they choose.
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0
Thank you for your assistance, Hoping you might be able to help me understand why I get this error when opening the exported sheet.

1682682196027.png


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.
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top