Partial loop

[bgonen]

With this code everything works fine with the exception of the blue fonts ; it stops the copy paste special when BancorpSouth starts with the loop.
Can someone help me please
Thanks

Sub Feed_invoices_FULL_LONG_version() 'LONG version 5.17.15


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim mydata As String
 Dim sPath As String
 Dim sFileName As String
 Dim sSheetName As String
 Dim pSheetName As String
 Dim sRange As String
 Dim res As String
Dim lastrow As Long, lastcolumn As Long
'Declare Arrays
 Dim arrWB() As Variant
 Dim thisWB As Workbook
 
'Clear contents in Control file
 
 Sheets("Control").Range("b4:d120, f4:h120,j4:j120").ClearContents
 'Constant Path
 sPath = "C:\VBA\Automating\"
 
'open Data file
 Workbooks.Open "C:\VBA\Automating\Data.xlsm"


'Message;close all workbooks
    MsgBox "Please make sure all forecast models are closed"
'In worksheet; forecast
    sSheetName = "Forecast"
    pSheetName = "Prior Forecast"
'message ; what column do you want to paste to ?
    res = InputBox("What column do you wish to paste to")
 
 'Array Models 1 thru 3
arrWB = Array("AFCU-automate.xlsx", "Alaska CU-automate.xlsx", "American Savings Bank-automate.xlsx", _
"Ameriprise-automate.xlsx", "AMEX-automate.xlsx", [COLOR=#a52a2a]"BancorpSouth-automate.xlsx"[/COLOR], "Bank Hapoalim-automate.xlsx", _
"Bank Leumi-automate.xlsx", "Bank of America-automate.xlsx", "Bank of China-automate.xlsx", "Bank of Stockton-automate.xlsx", _
"Bank of the West-automate.xlsx", "Bank United-automate.xlsx", "BB&T-automate.xlsx", "BECU-automate.xlsx", _
"Bell State Bank and Trust-automate.xlsx", "California CU-automate.xlsx", "Capital One-automate.xlsx", "Capitol Federal-automate.xlsx", _
"Central Pacific Bank-automate.xlsx", "Charles Schwab-automate.xlsx", "Citibank-automate.xlsx", "Citizens-automate.xlsx", "Coastal Federal-automate.xlsx", _
"Comerica-automate.xlsx", "Commerce-automate.xlsx", "Compass-automate.xlsx", "Delta Community CU-automate.xlsx", "Desert Schools-automate.xlsx", _
"Discover-automate.xlsx", "Eastern-automate.xlsx", "Edward Jones-automate.xlsx", "Fidelity Investments-automate.xlsx", "First Bank Data-automate.xlsx", _
"First Banks-automate.xlsx", "First Citizens-automate.xlsx", "First Niagara-automate.xlsx", "First Technology-automate.xlsx", "Flagstar-automate.xlsx", _
"FNB of PA-automate.xlsx", "Fulton Financial-automate.xlsx", "Golden One-automate.xlsx", "Goldman Sachs-automate.xlsx", "Green Dot-automate.xlsx", _
"HSBC-automate.xlsx", "ING-automate.xlsx", "Investors Bank-automate.xlsx", "JP Morgan Chase-automate.xlsx", "M&T-automate.xlsx", "Mellon-automate.xlsx", _
"Merrill Lynch-automate.xlsx", "Mission-automate.xlsx", "Morgan Stanley-automate.xlsx", "Mutual of Omaha-automate.xlsx", "National Penn-automate.xlsx", _
"Navy-automate.xlsx", "New York Community-automate.xlsx", "Northern Trust-automate.xlsx", "Old National-automate.xlsx", "OnPoint-automate.xlsx", _
"Pershing-automate.xlsx", "PNC-automate.xlsx", "Rabobank-automate.xlsx", "Randolph Brooks-automate.xlsx", "Raymond James-automate.xlsx", _
"RBC Georgia-automate.xlsx", "Regions Bank-automate.xlsx", "San Diego County CU-automate.xlsx", "Sandia Labs-automate.xlsx", "Santander-automate.xlsx", _
"Simple Bank-automate.xlsx", "Space Coast-automate.xlsx", "Stanford-automate.xlsx", "Suntrust-automate.xlsx", "Synovus-automate.xlsx", "TD Bank-automate.xlsx", _
"Technology CU-automate.xlsx", "Tri Counties Bank-automate.xlsx", "Umpqua-automate.xlsx", "Union Bank-automate.xlsx", "US Bank-automate.xlsx", _
"USAA-automate.xlsx", "Utah Community CU-automate.xlsx", "Wells Fargo-automate.xlsx")


'When loop thru Array, build up the full file name string
For wbI = LBound(arrWB) To UBound(arrWB)


'Build the Filename
sFileName = sPath & arrWB(wbI)


'open Model wbI
       Set thisWB = Workbooks.Open(filename:=sFileName)


'copy sum of invoice (SAP)
[COLOR=#0000ff]       thisWB.Sheets(sSheetName).Range("A1983:A1983").Copy[/COLOR]
[COLOR=#0000ff]       thisWB.Sheets(sSheetName).Range(res & 1983).PasteSpecial xlPasteValues[/COLOR]
[COLOR=#0000ff]    [/COLOR]
'Copy all automated metrics
[COLOR=#0000ff]       thisWB.Sheets(sSheetName).Range("A1999:b2045").Copy[/COLOR]
[COLOR=#0000ff]       thisWB.Sheets(sSheetName).Range(res & 1999).PasteSpecial xlPasteValues[/COLOR]
[COLOR=#0000ff]       [/COLOR]


       
'COPY RESULTS TO CONTROL (the rows.count, 6 for example is for the sixth column on the control sheet)
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 2).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range("I10").Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 3).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 1993).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 4).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 1989).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 7).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 14).Offset(0, 1).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 8).End(xlUp)(2).Value = thisWB.Sheets(pSheetName).Range(res & 14).Offset(0, 1).Value


'Save and Close Model
       thisWB.Saved = True
       thisWB.Close


    Next wbI
    
'Close the Data file without saving
    Application.EnableEvents = False
    Workbooks("Data.xlsm").Close SaveChanges:=False
    MsgBox "Feed was completed!"
End Sub

 

[AlphaFrog]

Can you better explain what "it stops" means; does it error (if yes, describe), does it ignore the copy\paste?

Does the file BancorpSouth-automate.xlsx have a worksheet named exactly Forecast with no trailing space at the end of the tab name?

 

[bgonen]

No error, it just ignore the copy/paste special values
I checked the worksheet tab; no trailing spaces.
Also, the file name ext is xlsx

 

[AlphaFrog]

I'm guessing you Paste special to a column to calculate something?

Does it paste zeros, or does it paste blanks, or does it paste the values but not calculate?

 

[bgonen]

It copy the formula so I can see the formula on cell (res & 1983) (but it should copy/paste/specialValues)
then the other range , A1999:B2045 : it stops making copy/pastespecial to area starting on cell (res&1999)

I wonder if it got to do with Dim res as String ?

 

[AlphaFrog]

You close thisWB without saving. How do you know it doesn't Copy\Paste Special Values to (res & 1999) for BancorpSouth-automate.xlsx?

I don't think the problem is Dim res as String.

 

[bgonen]

I changed the code slightly but I keep getting now an alert/message: Do you want to save the changes?
I want to save the changes, Close the workbook but without the displaying the alerts .

'Save and Close Model
thisWB.Close Savechanges:=True




Next wbI

 

[bgonen]

I think figured it out I used the following and looks like it work fine

'Save and Close Model
Application.DisplayAlerts = False
thisWB.Save
thisWB.Close False
Application.DisplayAlerts = False


Next wbI