98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Splitting tabs and safe in several separate workbook does not wo
Splitting tabs and safe in several separate workbook does not wo
Resolved · High Priority · Version 365
Annika has attended:
Excel VBA Introduction course
Excel Power Query course
Splitting tabs and safe in several separate workbook does not wo
Me again,
I am trying to create a VBA for automatically saving the file I am working on into several workbooks while keeping the original. However, when I do that, the main file always closes and I never get past the first new file.
In case it is of use, here are the macros:
Sub SAVE_CALUM_FILES()
'SAVE CALUM FILES'
ActiveWorkbook.SaveCopyAs "\\galbstorage\research\Research - PRIVATE\Data\Fixtures & Vessel Movements\US Whiteboard Reports\Calum Files.xls"
' Save a new file in the specified location
Dim FilePath As String
FilePath = "\\galbstorage\research\Research - PRIVATE\Data\Fixtures & Vessel Movements\US Whiteboard Reports\Calum Files"
Dim FileName As String
FileName = "IG Crude & Fuel Tracker - " & Format(Date, "ddmmyyyy") ' create the file name
ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False ' turn off pop up windows
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "CRD" And ws.Name <> "FO" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True ' turn on pop ups windows
End Sub
Sub SAVE_ECMex_FILES()
'SAVE ECMex FILES'
Workbooks("US Whiteboard Reports - Master").Activate
' Save a new file in the specified location
Dim FilePath As String
FilePath = "\\galbstorage\research\Research - PRIVATE\Data\Fixtures & Vessel Movements\US Whiteboard Reports\ECMex Files"
Dim FileName As String
FileName = "IG Crude & Fuel Tracker ECMex - " & Format(Date, "ddmmyyyy") ' create the file name
ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False ' turn off pop up windows
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "CRD ECMex" And ws.Name <> "FO ECMex" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True ' turn on pop ups windows
End Sub
Sub SAVE_Afra_BP_FILES()
'SAVE Afra BP FILES'
Workbooks("US Whiteboard Reports - Master").Activate
' Save a new file in the specified location
Dim FilePath As String
FilePath = "\\galbstorage\research\Research - PRIVATE\Data\Fixtures & Vessel Movements\US Whiteboard Reports\Afra BP Files"
Dim FileName As String
FileName = "Afra USG TA - " & Format(Date, "ddmmyyyy") ' create the file name
ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False ' turn off pop up windows
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Afra USG TA" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True ' turn on pop ups windows
End Sub
Thanks very much for your help!!
Brgds,
Annika
RE: Splitting tabs and safe in several separate workbook does no
Hi Annika
Thank you for using the forum to ask a questions.
I can see that we've responded to 2 questions already which I'm sure have helped you.
Please be aware that this question strays in to the consultancy frame as we are troubleshooting specific code problems you are having and not supporting with questions about the learning.
I will discuss with the Training Team if the best solution is for you to attend the next level of VBA rather than writing the solutions for you.
Please let me know if you are considering progressing your skills to the next level
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Splitting tabs and safe in several separate workbook does no
Thank you for your response via email, I'm glad you've managed to work around the issue.
We will now close this question
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quickly insert a functionIn Excel 97 and 2000 it was known as the Paste Function dialog box, these days it's known as the Insert Function dialog box. Regardless, one has to choose Insert|Function. or the fx button to open it up. There is, however, a non-mousey way to get hold of the Insert Function dialog box: press Shift+F3 in a blank cell to open the Insert Function dialog. |