Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2018, 09:44 AM
gbaker gbaker is offline Can't get Macro to work in This Workbook Windows 7 32bit Can't get Macro to work in This Workbook Office 2010 32bit
Competent Performer
Can't get Macro to work in This Workbook
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Can't get Macro to work in This Workbook


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
Reply With Quote
  #2  
Old 01-10-2018, 10:08 AM
Logit Logit is online now Can't get Macro to work in This Workbook Windows 10 Can't get Macro to work in This Workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #3  
Old 01-10-2018, 11:42 AM
gbaker gbaker is offline Can't get Macro to work in This Workbook Windows 7 32bit Can't get Macro to work in This Workbook Office 2010 32bit
Competent Performer
Can't get Macro to work in This Workbook
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Can't get Macro to work in This Workbook

Works Great, Thank you so much.
Reply With Quote
  #4  
Old 01-10-2018, 12:17 PM
Logit Logit is online now Can't get Macro to work in This Workbook Windows 10 Can't get Macro to work in This Workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

You are welcome.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't get Macro to work in This Workbook 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
Can't get Macro to work in This Workbook macro to transfer data from one workbook to another workbook virsojour Excel Programming 5 02-01-2011 08:58 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:52 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft