Can't get Macro to work in This Workbook

gbaker · #1 01-10-2018, 09:44 AM

I am trying to set up a macro so when the workbook is open the macro will run. I put the macro in This Workbook module but it doesn't appear to run when I open the workbook. What am I missing.

HTML Code:

Sub uploadmaster()
'
' uploadmaster Macro
'

'
    Workbooks.Open Filename:= _
        "https://sharepoint.fngn.com/departments/CED/CommunicationsDelivery/PrintProd/Reports%20Library/MasterTableDBase2018.xlsx"
    Columns("A:O").Select
    Selection.Copy
    Windows("Participants-Campaigns Actual and Projected 2018.xlsm").Activate
    Sheets("Master").Select
    Columns("A:A").Select
    Range("A5606").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ChDir "\\fngn.com\us\Projects\Print Production\Reports\"
        Workbooks.Open Filename:="\\fngn.com\us\Projects\Print Production\Reports\MasterTable2018.xlsx"
    Columns("A:O").Select
    Selection.Copy
    Windows("Participants-Campaigns Actual and Projected 2018.xlsm").Activate
    Sheets("Master").Select
    Columns("A:O").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("print2018").Select
    Application.CutCopyMode = False
    ActiveWorkbook.refreshall
    Sheets("print2018").Select
    Range("D21").Select
    Sheets("Averages").Select
    Range("C6").Select
    ActiveSheet.PivotTables("PivotTable4").PivotCache.refresh
    Sheets("Actual Participants from DSG").Select
    Range("AC9").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.refresh
    Sheets("Actual Members from DSG").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh
    Sheets("Actual Campaigns from DSG").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh
    Sheets("Participants-Projected-From PC").Select
    Range("H8").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.refresh
    Sheets("Campaigns-Projected-From PC").Select
    ActiveSheet.PivotTables("PivotTable4").PivotCache.refresh
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("update date macro").Select
    ChDir "\\fngn.com\us\Projects\Print Production\Reports\"
    Workbooks.Open Filename:="\\fngn.com\us\Projects\Print Production\Reports\MasterTable2018.xlsx"
    Columns("A:O").Select
    Selection.Copy
    Windows("Participants-Campaigns Actual and Projected 2018.xlsm").Activate
    Sheets("Master").Select
    Columns("A:O").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("print2018").Select
    Application.CutCopyMode = False
    ActiveWorkbook.refreshall
    Sheets("print2018").Select
    Range("D21").Select
    Sheets("Averages").Select
    Range("C6").Select
    ActiveSheet.PivotTables("PivotTable4").PivotCache.refresh
    Sheets("Actual Participants from DSG").Select
    Range("AC9").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.refresh
    Sheets("Actual Members from DSG").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh
    Sheets("Actual Campaigns from DSG").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh
    Sheets("Participants-Projected-From PC").Select
    Range("H8").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.refresh
    Sheets("Campaigns-Projected-From PC").Select
    ActiveSheet.PivotTables("PivotTable4").PivotCache.refresh
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("update date macro").Select
    Windows("MasterTable2018.xlsx").Activate
   
    ActiveWorkbook.Save
    ActiveWindow.Close
        
End Sub

Logit · #2 01-10-2018, 10:08 AM

Place your entire macro into a Routine Module (not in ThisWorkbook).

In ThisWorkbook paste this :

Code:

Option Explicit

Private Sub Workbook_Open()
    uploadmaster
End Sub

That will run the macro each time the workbook is opened.

gbaker · #3 01-10-2018, 11:42 AM

Works Great, Thank you so much.

Logit · #4 01-10-2018, 12:17 PM

You are welcome.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
One macro to sort individual sheets in a workbook	darbybrown	Excel Programming	1	09-11-2016 12:51 PM
Spell check macro within macro button field doesn't work in one document	samuelle	Word VBA	0	07-20-2016 02:27 AM
Personal Macro Workbook Does Not Load	WRowan	Excel	1	06-23-2016 08:26 AM
How do I assign a macro to a button when the macro is in my personal workbook?	foolios	Excel Programming	2	07-27-2011 02:41 PM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM