VB Script will not run and stops at Sub

Ray Rz

New Member
Joined
Jun 19, 2018
Messages
29
Code:
Sub Macro1()


For i = 1 To 500000




'
' Macro1 Macro
' Pull Data and Refresh
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
MsgBox ("Update may take several minutes,  Click Ok to begin")
      


    ChDir "C:\Users\measuser\Documents\HHI Current Month"
    Workbooks.Open Filename:= _
        "C:\Users\measuser\Documents\HHI Current Month\HHIEnergyInvoice.xlsx"
        
    Range("A2:P224").Copy
    'Selection.Copy
    'ActiveCell.Offset().Range("A2:P224").Select
    'Selection.Copy
    Windows("VBA Extractor r55 with code.xlsm").Activate
    Sheets("Invoice Summary").Select.ActiveCell.Offset().Range("A1:P224").Paste.ActiveSheet.Range("A2:P224").PasteSpecial Paste:=xlPasteValues
    
    
    
    
    
    Application.CutCopyMode = False




'
    ChDir "C:\Users\measuser\Documents\HHI Current Month"
    Workbooks.Open Filename:= _
        "C:\Users\measuser\Documents\HHI Current Month\HHIEnergyInvoiceDetailAdded.xlsx"
    ActiveCell.Offset(0, 0).Range("A2:AQ50000").Select
    Selection.Copy
    Windows("VBA Extractor r55 with code.xlsm").Activate
    Sheets("Invoice Summary").Select
    ActiveCell.Offset(0, 0).Range("A2:AQ50000").Select.ActiveSheet.PasteSpecial Paste = xlPasteValues
    
    
    Application.CutCopyMode = False
    '




    ChDir "C:\Users\measuser\Documents\HHI Current Month"
    Workbooks.Open Filename:= _
        "C:\Users\measuser\Documents\HHI Current Month\HHIMasterPoleSetFile.xlsx"
    ActiveCell.Offset(0, 0).Range("C2:AQ50000").Select
    Selection.Copy
    Windows("VBA Extractor r55 with code.xlsm").Activate
    Sheets("Invoice Summary").Select
    ActiveCell.Offset(0, 0).Range("C2").Select.ActiveSheet.PasteSpecial Paste = xlPasteValues
    
    
    Application.CutCopyMode = False
    
' Keyboard Shortcut: Ctrl+r






' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)


    ActiveWorkbook.RefreshAll
    
'  Refresh all Pivot tables in all worksheets


Dim shtTemp As Worksheet
Dim pvtTable As PivotTable
 
For Each shtTemp In ActiveWorkbook.Worksheets
    For Each pvtTable In shtTemp.PivotTables
        pvtTable.RefreshTable
    Next
Next


'
'    ChDir "C:\Users\measuser\Desktop\HHI Invoice Data Files"
'    ActiveWorkbook.SaveAs Filename:= _
'        "C:\Users\measuser\Desktop\HHI Invoice Data Files\VBA Extractor r56 with code.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True


MsgBox ("Update Complete,All data is Up-to date")




End Sub




End

Would any one be able to look at this and tell me why this will not run? It bombs out at the Sub. I am able to run it somewhat but cannot tie it together to completion.
Thank you,
Ray
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Ray Rz

New Member
Joined
Jun 19, 2018
Messages
29
I am trying to create a modue where it goes into my main file, and copies data into the file for 3 different copy/[astes, then it would run the refresh of all tables and say update complete.

Ray
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Try removing the word End after End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,509
Members
409,584
Latest member
RedHelp

This Week's Hot Topics

Top