Finalizing Report

Schwyl

New Member
Joined
Jan 25, 2014
Messages
23
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,295
VBA Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
You can do that just by copying the sheets. Such as this example:

VBA Code:
Dim arr, wb As Workbook

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

MsgBox wb.Name
 

Schwyl

New Member
Joined
Jan 25, 2014
Messages
23
VBA Code:
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



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

New Member
Joined
Jan 25, 2014
Messages
23

ADVERTISEMENT

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

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,295
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
So looking at what you have something like:

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

Forum statistics

Threads
1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

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