Code Issue

[Ray Rz]

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...

' 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

 

[bob_obo]

The version i supplied will compile. I don't think it'll get the correct result as the .copy and .pastespecial appear reversed though, eg.

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

I think should be;

Set currentWorkbook = ThisWorkbook
Set bookToCopyFrom = Workbooks.Open("C:\Users\myhomeusrname\Documents\HHI Current Month\HHIEnrInvoice.xlsx")

bookToCopyFrom.Sheets("Invoice Summary").Range("A2:P224").Copy
currentWorkbook.Sheets("Invoice").Range("A2:P224").PasteSpecial

 

[bob_obo]

Also its worth making FOR ... NEXT explicit, rather than

For i = 0 to 4
  for p = 0 to 2
   'do something
  next
next

Instead

For i = 0 to 4
  for p = 0 to 2
   'do something
  next p
next i

This way you can see which next relates to which for, and make sure next is always after the for which starts the loop.