VB Script will not run and stops at Sub

[Ray Rz]

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

 

[Ray Rz]

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]

Try removing the word End after End Sub

 

[Stiuart_W]

Also. You have a For i but no Next i