splitting tabs and safe

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum 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

resolvedResolved · 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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Quickly insert a function

In 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.

Press Shift+F3 after a function name and open bracket to open the Function Arguments dialog. For example, type =VLOOKUP( into a cell and press Shift+F3 to obtain a detailed description of VLOOKUP's arguments.

View all Excel hints and tips


Server loaded in 0.08 secs.