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

 

[jproffer]

You have one routine name ("Sub refresh"), so far so good.....but after that is where it goes astray.

You have at least 2 Next without a "For" above them, no less than 4 "End Sub"s unless I missed some, your dimensioning is all over the place (not that the code itself cares, but it makes it harder to read and debug)...most folks will DIM all their variables at the top unless they have to ReDim later.

I'm kind of wondering if your code didn't get WAY out of order somehow...but it's hard to tell because some of it looks pretty right and in sequence.

Maybe if you tell us what you want to do (you sort of did, I know) with as many details as possible like Workbook names, worksheet names, ranges to copy and the WB, WS, and range to paste THAT range....then the next range to copy and where to paste THAT range....and so on. If you can do that, someone will have a better chance of helping you correct (or maybe re-write) your code.

 

[Ray Rz]

Thanks so much for answering me. I am going a little crazy over this code. Essentially, I want to create procedure where my Master file goes out to 4 differing files for a data table refresh, then runs the refresh script and updates all my tables. So far today, I have rebuilt my tables twice. It could be a simple solution but I am pretty new and just essentially want to know how I would map it, set it up, make it copy from 3-4 different files, close the workbooks that I opened and then save the file as the same name after the refresh table script runs.

Any additional help or a better way to do it would be appreciated. I had it setup with objects and I confused myself even more so that was a bad idea on my part.

Thanks again for any help.

Ray

 

[Ray Rz]

Here are the details of what I am trying to do. Thank you so much for your help...

currentWorkbook.Sheets("Invoice").Range("A2:P224").Copy
bookToCopyFrom.Sheets("Invoice Summary").Range("A2:P224").PasteSpecial
File to pull from: bookToCopyFrom -> C:\Users\myhomeusrname\Documents\HHI Current Month\HHIEnrInvoice.xlsx
Tab from name: Invoice Summary
Tab in my worksheet: Invoice
Range is the same as from/to for copy




currentWorkbook.Sheets("Invoice").Range("A2:AQ75000").Copy
bookToCopyFrom.Sheets("Invoice Summary").Range("A2:AQ75000").PasteSpecial
File to pull from: bookToCopyFrom -> C:\Users\myhomeusrname\Documents\HHI Current Month\HHIEnrInvoiceDetailAdded.xlsx
Tab from name: HHI Master
Tab in my worksheet: HHI Master
(Tabs are named the same in both worksheets)
Range is the same as from/to for copy


I need help to launch the VB, run the file copy into my worksheet, then run my refresh tables script.
If you could help, I would appreciate it very much.


Thank you,
Ray

 

[bob_obo]

Sub refresh()    ' Refresh all applicable pivot tables to setup month's data


Dim currentWorkbook As Workbook
Dim bookToCopyFrom As Workbook




'
' refresh Macro after data moved from reporting files
' refresh data
'
' Keyboard Shortcut: Ctrl+r
MsgBox ("Update may take several minutes,  Click Ok to begin")


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






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


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






' 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

 

[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

This is perfect. Thank you very much for your assistance.
Ray

 

[Ray Rz]

I am getting a Compile error (Next without For) with this script. Any suggestions on how to fix this module?

Ray

 

[jproffer]

There are 3 Nexts without For. I see one For/Next loop at the bottom, but there are 3 Nexts above that.

 

[Ray Rz]

So how can I fix it? I am trying to get this corrected but having an issue with the best way...
Ray

 

[jproffer]

Did you try the code in post #5 ? And is that the one that "worked perfect" in post #6 ...and also the one with a compile error in post #7 ?

I'm not trying to be smart, believe it or not, I just want to make sure that 1) you saw it, and tried it...2) if it ever really did "work perfectly" ...and 3) if it IS the one that has a compile error now. If it worked perfectly, but now has a compile error then something, needless to say, changed and needs to be recopied from Post #5 . I ask also because that's not the one you quoted when you said it worked perfectly, but you quoted post #1 , which I have to assume did not work perfectly or you wouldn't be here...and post #5 was the only other attempt so far.

I can tell you this much...Sometimes the compiler doesn't know what it's problem is. For instance, if you do this:

For i = 1 To 20
    If i = 5 Then
        MsgBox "i = 5"
Next i

It could very well tell you have a Next without For because you didn't close the IF statement and the last keyword it sees before "Next" is "If" so it assumes there is no "For" . I'm not smart enough to know if there's even a way MS could ever fix this issue, but it's always been an issue.

Point being, make sure every thing is closed in order. Yet another reason to indent your code.

As far as the code in post #5 , I don't see any of those problems. I did notice that this line:

pvtTable.RefreshTable

is commented out, so if you need to refresh those pivot tables, remove the comment tag (the " ' "...single quote)