Splashscreen transfer in macro

[Jemma Atkinson]

Hi i have a splashscreen in the active workbook, how can i get the macro to transfer the splashscreen when creating a new workbook with the following sheets. I dont think its possible without manually transfering the spashscreen everytime i run the below code?

Sub Save_File()
    
    Application.ScreenUpdating = False
        
    Sheets(Array("SUMMARY", ">30_DAYS_CASH")).Copy

    With ActiveWorkbook
    
        .Sheets("SUMMARY").Cells.Copy
        .Sheets("SUMMARY").Cells(1).PasteSpecial xlPasteValues
        .Sheets(">30_DAYS_CASH").Cells.Copy
        .Sheets(">30_DAYS_CASH").Cells(1).PasteSpecial xlPasteValues
         .Sheets("CASH_AUD_100K_999K").Cells.Copy
        
        
        .SaveAs Filename:="S:RECS\Reporting\Stats\Summary Stats over 30 days\" & Format(Date, "yyyy\\MMM") & _
        "\Stat Summary_" & Format(Date, "dd-mm-yy") & ".xls"
        .Close False
    End With
    
    Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

If by splashscreen you mean a UserForm, instead of copying the sheets to a new workbook, delete the sheets you dont want then SaveAs.

 

[Jemma Atkinson]

If by splashscreen you mean a UserForm, instead of copying the sheets to a new workbook, delete the sheets you dont want then SaveAs.

Yep i have a userform, the wkbk has 12 worksheets, how do i amend the code to say delete all sheets except for these?

("SUMMARY", ">30_DAYS_CASH"

 

[mooseman]

This should get you started, it may need some tweaking (like the popup message)

Sub Save_File()
    
    Application.ScreenUpdating = False
       
Dim i As Integer

Let i = 1

Do While i <= Worksheets.Count
  If Worksheets(i).Name <> "SUMMARY" And Worksheets(i).Name <> ">30_DAYS_CASH"  Then
   Worksheets(i).Delete
  Else
      i = i + 1
   End If

Loop 
   
    With ActiveWorkbook
                 
        .SaveAs Filename:="S:RECS\Reporting\Stats\Summary Stats over 30 days\" & Format(Date, "yyyy\\MMM") & _
        "\Stat Summary_" & Format(Date, "dd-mm-yy") & ".xls"
        .Close False
    End With
    
    Application.ScreenUpdating = True
End Sub

 

[Jemma Atkinson]

Question, i have formulas in my active workbook, when i run the below i get #REF in the new workbook

This should get you started, it may need some tweaking (like the popup message)

Sub Save_File()
 
    Application.ScreenUpdating = False
 
Dim i As Integer
 
Let i = 1
 
Do While i <= Worksheets.Count
  If Worksheets(i).Name <> "SUMMARY" And Worksheets(i).Name <> ">30_DAYS_CASH"  Then
   Worksheets(i).Delete
  Else
      i = i + 1
   End If
 
Loop 
 
    With ActiveWorkbook
 
        .SaveAs Filename:="S:RECS\Reporting\Stats\Summary Stats over 30 days\" & Format(Date, "yyyy\\MMM") & _
        "\Stat Summary_" & Format(Date, "dd-mm-yy") & ".xls"
        .Close False
    End With
 
    Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

Do your copy/paste special values before deleting the unwanted worksheets.

 

[mooseman]

Yep. I missed that.....
Just add that back in and you should be good to go.
Hope this helped.