I created a Module in VB, tried to go into my working file, copy data from 3 other sources, and then copy back to my file. Here is my code and if anyone has a chance and could look at this, I would appreciate it so much...
Code:
' Create directory called HHI Current Month in C:\Users\myhomeusrname\Documents\ directory
' Rename pole set file to HHIMasterPleSetFile.xlsx name convention
' Rename only tab to be called HHI Master
' Rename invoice file to HHIEnrInvoice.xlsx name convention
' Rename Requistion for Payment (1st tab) to be called DetailInvoice
' Rename detailed invoice file to HHIEnrInvoiceDetailAdded
' Rename 1st Sheet to be called the name Invoice
'
Sub refresh()
' Refresh all applicable pivot tables to setup month's data
'
' refresh Macro after data moved from reporting files
' refresh data
'
' Keyboard Shortcut: Ctrl+r
MsgBox ("Update may take several minutes, Click Ok to begin")
Dim currentWorkbook As Workbook
Dim bookToCopyFrom As Workbook
Set currentWorkbook = ThisWorkbook
Set bookToCopyFrom = Workbooks.Open("C:\Users\myhomeusrname\Documents\HHI Current Month\HHIEnrInvoice.xlsx")
currentWorkbook.Sheets("Invoice").Range("A2:P224").Copy
bookToCopyFrom.Sheets("Invoice Summary").Range("A2:P224").PasteSpecial
bookToCopyFrom.Close
Next
Set currentWorkbook = ThisWorkbook
Set bookToCopyFrom = Workbooks.Open("C:\Users\myhomeusrname\Documents\HHI Current Month\HHIEnrInvoiceDetailAdded.xlsx")
currentWorkbook.Sheets("Invoice").Range("A2:AQ75000").Copy
bookToCopyFrom.Sheets("Invoice Summary").Range("A2:AQ75000").PasteSpecial
bookToCopyFrom.Close
End Sub
Next
Set currentWorkbook = ThisWorkbook
Set bookToCopyFrom = Workbooks.Open("C:\Users\myhomeusrname\Documents\HHI Current Month\HHIMasterPoleSetFile.xlsx")
currentWorkbook.Sheets("HHI Master").Range("A2:AQ75000").Copy
bookToCopyFrom.Sheets("HHI Master").Range("A2:AQ75000").PasteSpecial
bookToCopyFrom.Close
Next
' Refresh all applicable pivot tables to setup month's data
'
' refresh Macro after data moved from reporting files
' refresh data
'
' Keyboard Shortcut: Ctrl+r
MsgBox ("Update may take several minutes, Click Ok to begin")
' 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
MsgBox ("Update Complete,All data is Up-to date")
End Sub
End Sub
End Sub
Last edited by a moderator: