Wsrtjtyk

I am still relatively new to VBA and not too experienced with the Application.OnTime method. I have been looking at multiple resources, and I cannot see a clear way to automatically run a macro daily without opening any workbooks (assuming I run the macro first in my workbook).

Is this possible? I would like to be more efficient instead of running multiple macros every morning.

Let me know if you need more info but my code would simply be a copy/paste process:

Sub MyMacro()

Application.ScreenUpdating = False



Dim OH As Workbook

Dim PO As Workbook





Set OH = Workbooks.Open("filepath")

Set PO = Workbooks.Open("filepath2")



'clear sheet

ThisWorkbook.Sheets("OH").Range("A2:O10000").ClearContents

'clear other sheet

ThisWorkbook.Sheets("OP").Range("A2:AG10000").ClearContents





'Paste new data

OH.Sheets("OH").Range("B3:P10000").Copy 

Destination:=ThisWorkbook.Sheets("OH").Range("A2")

PO.Sheets("OP").Range("A3:AG20000").Copy 

Destination:=ThisWorkbook.Sheets("OP").Range("A2")





OH.Close savechanges:=False

PO.Close savechanges:=False





'Refresh all pivot tables

Dim PT As PivotTable

Dim WST As Worksheet

    For Each WST In ThisWorkbook.Worksheets

    For Each PT In WST.PivotTables

        PT.RefreshTable

    Next PT

Next WST





'Clear last sheet

ThisWorkbook.Sheets("Pivot1 paste").Range("A6:E10000").ClearContents



ThisWorkbook.Sheets("Pivot1").Range("A6:D10000").Copy 

Destination:=ThisWorkbook.Sheets("Pivot1 paste").Range("A6")



'Paste variable column to last sheet

Dim cell As Range

For Each cell In ThisWorkbook.Sheets("Pivot1").Range("E3:AZ6")

    If cell.Value = "Out" Then cell.EntireColumn.Copy 

Destination:=ThisWorkbook.Sheets("Pivot1 paste").Columns(5)

Next



'Save with current date and close

ThisWorkbook.SaveAs ("TargetFilepath") 

& ".xlsm")

ThisWorkbook.Close





Application.ScreenUpdating = True

End Sub

VBA is made to work within Microsoft Office, but you can leverage VB Script to open a workbook and run a macro.

Place the following in a .vbs file. You will create a schedule to call and execute this file. Make sure to set the correct path and edit "test.xlsm!mymacro" to the name of your workbook and the macro you wish to call.

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:pathtotest.xlsm")



objExcel.Application.Run "test.xlsm!mymacro"

objExcel.ActiveWorkbook.Close



objExcel.Application.Quit

WScript.Echo "Finished."

WScript.Quit

To test this, I created a quick macro in test.xlsm to create a text file in the same directory and verified that it was there after I ran the VB Script. There is nothing special about the macro below, you can call whatever macro you desire. This will save you from having to manually open the workbook and run the macro.

Sub mymacro()

    Dim fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim oFile As Object

    Set oFile = fso.CreateTextFile("C:UsersbcohanDownloadstesting.txt")



    oFile.WriteLine "test"

    oFile.Close



    Set fso = Nothing

    Set oFile = Nothing

End Sub

Once you have the above working, you should be able to create a scheduled task in windows to run your script.