Why aren't you on Vacation?

I would use Task scheduler to open the workbook when scheduled rather than
goof around with a whole bunch of OnTime coding which excludes the weekend
dates.

I have no clue how to code OnTime to miss certain days.

But Chip Pearson has some sample OnTime code at his site.

http://www.cpearson.com/excel/OnTime.aspx

Maybe you can cobble something together.

For Task scheduler........................

Have your code in Thisworkbook module and use Workbook_Open event.

For the emailing part see Ron de Bruin's site for code.

http://www.rondebruin.nl/sendmail.htm


Gord

On Sat, 12 Dec 2009 06:45:01 -0800, Kam
wrote:

Hello,

any luck with the below.

Best Regards,
Kam

"Kam" wrote:

Hi,

Thanks for your reply..Yes I can keep Excel open & macro file as well.

This seems to me good idea...I would like to run a macro weekly except
Saturday & Sunday...

Best Regards,
Kam.


"Gord Dibben" wrote:

Will Excel be running at all times?

Will the workbook with the macros be open at all times?

A macro can be run at a certain time using the OnTime method but Excel and
the workbook must be open.

Alternative..................

Windows Task Scheduler to start Excel at a given time with the workbook to
open and Workbook_Open code to do the macro work is one method.

You would not have to use OnTime method.


Gord Dibben MS Excel MVP


On Wed, 9 Dec 2009 03:28:01 -0800, Kam
wrote:

Hello,

Sorry for chasing..I am going on holiday from Tomorrow..It would be great if
any can guide me or give me the solution to below request.

Best Regards,
Kam.

"Kam" wrote:

Dear all,

I am going for vacation in two days. I need to run my VBA code automatically.

Everyday, I get one excel workbook by using another macro. This workbook
contains some values.

I have written a vba code in Excel_Macro.xls which does the work for me.
This vba code generates Pivot table in sheets in to Excel_Macro.xls.

Script In Excel_Macro.xls
Sheets("Pivot_Table").Select

With Worksheets("Pivot_Table")

Range("A:K").Select
With Selection
Cells.Clear
End With

Dim PT_9_Cache As PivotCache, PT_9 As PivotTable
Set PT_9_Cache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="Temp_Data!A:AK")
Set PT_9 =
PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_Table!R1C1",
TableName:="FACT")
With PT_9
.PivotFields(1).Orientation = xlRowField 'Department
.PivotFields(2).Orientation = xlRowField 'Status
.PivotFields(3).Orientation = xlDataField 'SeqNo
End With
With ActiveSheet.PivotTables("FACT").PivotFields("Depar tment")
.Orientation = xlRowField
.Position = 1
End With
Range("A2").Select
ActiveSheet.PivotTables("FACT").PivotFields("Count of
SeqNo").Function = xlCount
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B2").Select
ActiveSheet.PivotTables("FACT").PivotFields("Statu s").Subtotals = _
Array(False, False, False, False, False, False, False, False, False,
False, False, False)
Range("A2").Select
With ActiveSheet.PivotTables("FACT")
.ColumnGrand = False
.RowGrand = False
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
End With

Range("A:K").Select
With Selection
.Font.Size = 10
.Font.Name = "Verdana"
End With

Selection.Columns.AutoFit

End Sub

Once this script is done I want macro to send it by email to sender list.Can
this be done...I would appreciate your help in this Matter.

Best Regards,
Kam.

.