Automate Excel-Based Report etc. 1

[MwTV]

Assistance needed for task that I am not able to quite handle!

Trying to automate daily MS Excel-based Report using an Excel template that is based on a query of an external data source that is set to refresh automatically.

How should the following code be modified if I want to perform the following upon opening the Excel template;

* Automate pages setup (columns will always be "A" to "Q" but the number of records will vary);

* Populate cell A2 with today's date;

* Delete all macros (prior to distribution);

* Save to local folder with a filename that is appended with today's date. For example, "C:\Reports\ClientReport_Today'sDate.xls"

* and E-mail to manager (Jane Smith) via MS Outlook and cc Supervisor, John Smith.


So far, I have the following code.

Any insight to point me in the right direction is greatly appreciated!

Sub PageSetup()
Dim ws As Worksheet
Set ws = ActiveSheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ws.PageSetup
.Orientation = xlLandscape
.Zoom = False ' Force to use fit to page
.FitToPagesWide = 1
.FitToPagesTall = 1
'Do not print the grid lines on the printout
.PrintGridlines = False

'Set left margin to 0.25 inches
.LeftMargin = Application.InchesToPoints(0.25)
'Set right margin to 0.25 inches
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.PaperSize = xlPaperA4
'Print the first 3 rows as the header on each sheet
.PrintTitleRows = appXl.ActiveSheet.Range("A1:A4").Address
.PrintArea = "$A$1:$Q25$"

End With
With Application
.Calculation = xlCalculationAutomatic
.Calculate 'Force Excel to calculate the workbook.
.ScreenUpdating = True
End With
ws.PrintOut
End Sub

 

[SkipVought]

MwTV,

Please post VBA questions in Forum707.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]