Splashscreen transfer in macro

Jemma Atkinson

Well-known Member
Joined
Jul 7, 2008
Messages
509
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?

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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Jul 7, 2008
Messages
509
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

Board Regular
Joined
Jul 23, 2004
Messages
195
This should get you started, it may need some tweaking (like the popup message)

Code:
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

Well-known Member
Joined
Jul 7, 2008
Messages
509

ADVERTISEMENT

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)

Code:
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
 

mooseman

Board Regular
Joined
Jul 23, 2004
Messages
195
Yep. I missed that.....
Just add that back in and you should be good to go.
Hope this helped.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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
Top