Billdub417
New Member
- Joined
- Nov 5, 2019
- Messages
- 45
Hello,
Please see code below - I currently use this to export a hidden tab from a master document.
However, there are 2 main issues with it, as follows:
1) Is it possible to set a name that this exported book saves as, i.e. "Book1", and if so, can this be set so that next time it will be "Book2" etc? (it currently comes up blank)
2) If this data is exported and someone clicks "cancel" rather than "save" when choosing the location for this new document, the debug error message comes up, highlighting the "Sheets("Data").Visible = False" line (and not closing the exported tab) - is it possible to get it to close the exported document and still hide the "Data" tab on the master document?
Thanks in advance!
Please see code below - I currently use this to export a hidden tab from a master document.
However, there are 2 main issues with it, as follows:
1) Is it possible to set a name that this exported book saves as, i.e. "Book1", and if so, can this be set so that next time it will be "Book2" etc? (it currently comes up blank)
2) If this data is exported and someone clicks "cancel" rather than "save" when choosing the location for this new document, the debug error message comes up, highlighting the "Sheets("Data").Visible = False" line (and not closing the exported tab) - is it possible to get it to close the exported document and still hide the "Data" tab on the master document?
Thanks in advance!
VBA Code:
Sub new book()
Sheets("Data").Visible = True
Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsx), *.xlsx")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
ActiveWorkbook.Close
Sheets("Data").Visible = False
End Sub