Finalizing Report

[Schwyl]

Hey Everyone,

First off thank you to everyone who posts in this forum as it made my attempt at this work almost as expected. Just need a little help getting over the finish line.

The goal of the original excel file called (ReportGenerator.xlsm) is to be called from a script to run at certain intervals. <- This piece I have and it works
Reportgenerator.xlsm does a bunch of stuff but at the end I'm left with 5 report tabs ( Report1, Report2, Report3,Report4,Report5

Now what im trying to do and this is where i'm getting lost.

My final sub is what I called Reportcleanup

When I run that sub I want to Save the ReportGenerator.xlsm as a new Excel workbook without macros and only the 5 report tabs and call it Report.xlsx with the 5 report tabs in it.

Can anyone help me get going on the right path?

 

[Logit]

Option Explicit

Sub SaveMe()

Dim newFileFullName As String
newFileFullName = "C:\Users\My\Desktop\List.xlsx"               '<--- edit path & file name as required
Application.DisplayAlerts = False

ActiveSheet.Shapes("CommandButton1").Visible = False

ActiveWorkbook.SaveAs Filename:=newFileFullName, FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveSheet.Shapes("CommandButton1").Visible = True

Windows("List.xlsx").Application.Quit                           '<-- edit filename as required
Windows("List.xlsx").Visible = False                            '<-- edit filename as required

End Sub

 

[steve the fish]

You can do that just by copying the sheets. Such as this example:

Dim arr, wb As Workbook

arr = Array("Sheet1", "Sheet2", "Sheet3")
Sheets(arr).Copy
Set wb = ActiveWorkbook

MsgBox wb.Name

 

[Schwyl]

Option Explicit

Sub SaveMe()

Dim newFileFullName As String
newFileFullName = "C:\Users\My\Desktop\List.xlsx"               '<--- edit path & file name as required
Application.DisplayAlerts = False

ActiveSheet.Shapes("CommandButton1").Visible = False

ActiveWorkbook.SaveAs Filename:=newFileFullName, FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveSheet.Shapes("CommandButton1").Visible = True

Windows("List.xlsx").Application.Quit                           '<-- edit filename as required
Windows("List.xlsx").Visible = False                            '<-- edit filename as required

End Sub

Thank you for that. Maybe I should show you what I was trying

Sub SaveAsCleanReport()

' Setting up Filename and Path
Filename = Format(Now, "yyyy-mm-dd HH mm ss AMPM") & ".xlsx"
Path = "C:\"

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

Sheets("ReportsData1").Delete
Sheets("ReportsData2").Delete
Sheets("ReportsData3").Delete
ActiveWorkbook.Sheets(Array("Report1", "Report2","Report3","Report4","Report4")).Copy

' Saving Current workbook as Dated File
ActiveWorkbook.SaveAs Filename:=Path + Filename, FileFormat:=xlOpenXMLWorkbook



    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    
End Sub

 

[Schwyl]

You can do that just by copying the sheets. Such as this example:

Dim arr, wb As Workbook

arr = Array("Sheet1", "Sheet2", "Sheet3")
Sheets(arr).Copy
Set wb = ActiveWorkbook

MsgBox wb.Name

I want this all to be silent in the back ground.

 

[steve the fish]

What i gave would create a new workbook with the sheets named in the array. It then assigns it to a variable. After that you just need to save it to whatever location you want it saved in and close it with the variable.

 

[Logit]

Your request said you would be left with the 5 Report Tabs, of which you want to save the entire workbook without any macros.
The macro code I submitted will do just that.

What other options are you seeking ?

 

[steve the fish]

So looking at what you have something like:

Dim arr, path As String, fname As String

path = "something\"
fname = "somethingelse.xlsx"

arr = Array("Report1", "Report2", "Report3", "Report4", "Report5")
Sheets(arr).Copy
With ActiveWorkbook
    .SaveAs Filename:=path & fname, FileFormat:=xlOpenXMLWorkbook
    .Close False
End With