Optimize/speed-up my large VBA routine for obtaining data?

NessPJ · Jun 28, 2013

Hello there,

I have been working on a sheet that obtains data from other files that are spread out over my network/cloud.
All of the functionality can be defined by the user in a seperate sheet, where they can specify which path, filename, sheet and cell or column to look for.
This works fine, yet (taking network/file latency etc. into account) the routine is rather slow and takes around 3,5 minutes.
Sure, there is a lot of data to be obtained, but i was just wondering if i might be doing something inefficiently and could improve on.

Sidenote: The code uses a lot of Cell linking or VLOOKUP functions rather than the 'INDIRECT' functions, because INDIRECT always requires the file to be opened when a link is made.
Sidenote 2: at the end of the routine, links are converted back to values to keep the file as small and fast as possible (even with calculation on).

Here's the code:

Code:

Sub UpdateDashboard()</SPAN>
 
'Deze Routine start alle code welke nodig is om het Dashboard bij te werken.</SPAN>
'De code is opgesplitst in een aantal onderdelen om het overzicht te behouden alsmede te zorgen dat de routines niet te lang worden.</SPAN>
'(Routines mogen niet groter zijn dan 64 kb).</SPAN>
'=============================================</SPAN></SPAN>
 
Call Start</SPAN></SPAN>
 
Call RetrieveData</SPAN></SPAN>
 
Call RemoveFormulas</SPAN></SPAN>
 
Call Finish</SPAN></SPAN>
 
End Sub</SPAN></SPAN>

 
Private Sub Start()</SPAN></SPAN>
 
    'Start of routine</SPAN></SPAN>
    '=================</SPAN></SPAN>
   
    Application.ScreenUpdating = False</SPAN></SPAN>
    Application.DisplayAlerts = False</SPAN></SPAN>
   
    Sheets("Dashboard").Unprotect ("1234")</SPAN></SPAN>
   
    Application.Calculation = xlCalculationAutomatic</SPAN></SPAN>
        
   
    'Plaats Datum + Tijd in Cell B14 (Query Start Info):</SPAN></SPAN>
    '=================================================================</SPAN></SPAN>
 
    Sheets("Parameters").Range("B14").Formula = "=Now()"</SPAN></SPAN>
    Call PasteValues("Parameters", "B14")</SPAN></SPAN>
   
    '=================================================================</SPAN></SPAN>
   
End Sub</SPAN></SPAN>

 
 
Private Sub RetrieveData()</SPAN></SPAN>
 
    On Error Resume Next</SPAN></SPAN>
   
   
    'Variabelen definiëren:</SPAN></SPAN>
    '======================</SPAN></SPAN>
   
    Dim strPath As String</SPAN></SPAN>
    Dim strFile As String</SPAN></SPAN>
    Dim strSheet As String</SPAN></SPAN>
    Dim strCell As String</SPAN></SPAN>
    Dim strFormula As String</SPAN></SPAN>
    Dim strTable As String</SPAN></SPAN>
    Dim strColumn As String</SPAN></SPAN>
    Dim strTarget As String</SPAN></SPAN>
   
   
    strOffset = Sheets("Parameters").Range("S4").Value</SPAN></SPAN>
  
   
    'Bezetting Hoogbouw</SPAN></SPAN>
    '===================</SPAN></SPAN>
   
    Call GetData(Sheets("Parameters").Range("F18").Value, Sheets("Parameters").Range("F19").Value, Sheets("Parameters").Range("F20").Value, Sheets("Parameters").Range("F21").Value, "B18")</SPAN></SPAN>
   
 
    'Ophalen prestaties + normen:</SPAN>
    '====================================================================================</SPAN>
   
   
    'Urenstaat in kwestie wordt geopend, zodat data sneller kan worden opgehaald.</SPAN>
    'De cellen F24 + F25 op Sheet "Parameters" worden gebruikt om de juiste Urenstaat te vinden.</SPAN>
    '============================================================================================</SPAN></SPAN>
   
    Workbooks.Open Filename:=Sheets("Parameters").Range("F24").Value & Sheets("Parameters").Range("F25").Value, UpdateLinks:=0, ReadOnly:=1, IgnoreReadOnlyRecommended:=1</SPAN></SPAN>
 
    Workbooks(Dashboard).Activate</SPAN></SPAN>
   
   
    'AH Picking rate</SPAN></SPAN>
    '================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F24").Value, Sheets("Parameters").Range("F25").Value, Sheets("Parameters").Range("F26").Value, Sheets("Parameters").Range("F27").Value, "B24")</SPAN></SPAN>
   
    'AH Picking norm</SPAN></SPAN>
    '================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F24").Value, Sheets("Parameters").Range("F25").Value, Sheets("Parameters").Range("F26").Value, Sheets("Parameters").Range("L27").Value, "B25")</SPAN></SPAN>
 
   
    'AvG Picking rate</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F29").Value, Sheets("Parameters").Range("F30").Value, Sheets("Parameters").Range("F31").Value, Sheets("Parameters").Range("F32").Value, "B29")</SPAN></SPAN>
   
    'AvG Picking norm</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F29").Value, Sheets("Parameters").Range("F30").Value, Sheets("Parameters").Range("F31").Value, Sheets("Parameters").Range("L32").Value, "B30")</SPAN></SPAN>
   
   
    'ALB Picking rate</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F34").Value, Sheets("Parameters").Range("F35").Value, Sheets("Parameters").Range("F36").Value, Sheets("Parameters").Range("F37").Value, "B34")</SPAN></SPAN>
   
    'ALB Picking norm</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F34").Value, Sheets("Parameters").Range("F35").Value, Sheets("Parameters").Range("F36").Value, Sheets("Parameters").Range("L37").Value, "B35")</SPAN></SPAN>
 
   
    'Replenishment rate</SPAN></SPAN>
    '===================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F44").Value, Sheets("Parameters").Range("F45").Value, Sheets("Parameters").Range("F46").Value, Sheets("Parameters").Range("F47").Value, "B44")</SPAN></SPAN>
   
    'Replenishment norm</SPAN></SPAN>
    '===================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F44").Value, Sheets("Parameters").Range("F45").Value, Sheets("Parameters").Range("F46").Value, Sheets("Parameters").Range("L47").Value, "B45")</SPAN></SPAN>
 
   
    'ALB Replenishment rate</SPAN></SPAN>
    '=======================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F49").Value, Sheets("Parameters").Range("F50").Value, Sheets("Parameters").Range("F51").Value, Sheets("Parameters").Range("F52").Value, "B49")</SPAN></SPAN>
   
    'ALB Replenishment norm</SPAN></SPAN>
    '=======================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F49").Value, Sheets("Parameters").Range("F50").Value, Sheets("Parameters").Range("F51").Value, Sheets("Parameters").Range("L52").Value, "B50")</SPAN></SPAN>
 
   
    'ALB Assembly rate</SPAN></SPAN>
    '==================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F60").Value, Sheets("Parameters").Range("F61").Value, Sheets("Parameters").Range("F62").Value, Sheets("Parameters").Range("F63").Value, "B60")</SPAN></SPAN>
   
    'ALB Assembly norm</SPAN></SPAN>
    '==================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F60").Value, Sheets("Parameters").Range("F61").Value, Sheets("Parameters").Range("F62").Value, Sheets("Parameters").Range("L63").Value, "B61")</SPAN></SPAN>
 
   
    'Pallets IN Rate</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F70").Value, Sheets("Parameters").Range("F71").Value, Sheets("Parameters").Range("F72").Value, Sheets("Parameters").Range("F73").Value, "B70")</SPAN></SPAN>
   
    'Pallets IN Norm</SPAN></SPAN>
    '==================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F70").Value, Sheets("Parameters").Range("F71").Value, Sheets("Parameters").Range("F72").Value, Sheets("Parameters").Range("L73").Value, "B71")</SPAN></SPAN>
   
    'Pallets IN Total</SPAN></SPAN>
    '==================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F70").Value, Sheets("Parameters").Range("F71").Value, Sheets("Parameters").Range("F72").Value, Sheets("Parameters").Range("S71").Value, "S72")</SPAN></SPAN>
 
   
   
    'Pallets OUT Rate</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F75").Value, Sheets("Parameters").Range("F76").Value, Sheets("Parameters").Range("F77").Value, Sheets("Parameters").Range("F78").Value, "B75")</SPAN></SPAN>
   
    'Pallets OUT Norm</SPAN></SPAN>
    '==================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F75").Value, Sheets("Parameters").Range("F76").Value, Sheets("Parameters").Range("F77").Value, Sheets("Parameters").Range("L78").Value, "B76")</SPAN></SPAN>
   
    'Pallets OUT Total</SPAN></SPAN>
    '==================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F75").Value, Sheets("Parameters").Range("F76").Value, Sheets("Parameters").Range("F77").Value, Sheets("Parameters").Range("S76").Value, "S77")</SPAN></SPAN>
 
   
    'Rolc. IN Rate</SPAN></SPAN>
    '==============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F80").Value, Sheets("Parameters").Range("F81").Value, Sheets("Parameters").Range("F82").Value, Sheets("Parameters").Range("F83").Value, "B80")</SPAN></SPAN>
 
    'Rolc. IN Norm</SPAN></SPAN>
    '==============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F80").Value, Sheets("Parameters").Range("F81").Value, Sheets("Parameters").Range("F82").Value, Sheets("Parameters").Range("L83").Value, "B81")</SPAN></SPAN>
   
   
    'Rolc. OUT Rate</SPAN></SPAN>
    '===============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F85").Value, Sheets("Parameters").Range("F86").Value, Sheets("Parameters").Range("F87").Value, Sheets("Parameters").Range("F88").Value, "B85")</SPAN></SPAN>
   
    'Rolc. OUT Norm</SPAN></SPAN>
    '===============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F85").Value, Sheets("Parameters").Range("F86").Value, Sheets("Parameters").Range("F87").Value, Sheets("Parameters").Range("L88").Value, "B86")</SPAN></SPAN>
 
    'Rolc. OUT Total</SPAN></SPAN>
    '===============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F85").Value, Sheets("Parameters").Range("F86").Value, Sheets("Parameters").Range("F87").Value, Sheets("Parameters").Range("S86").Value, "S87")</SPAN></SPAN>
 
   
    '====================================================================================</SPAN>
   
   
    'Ophalen uren (urenstaat)</SPAN>
    '====================================================================================</SPAN>
 
    'Niet productief - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F117").Value, Sheets("Parameters").Range("F118").Value, Sheets("Parameters").Range("F119").Value, Sheets("Parameters").Range("F120").Value, "B118")</SPAN></SPAN>
   
    'Niet productief - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F117").Value, Sheets("Parameters").Range("F118").Value, Sheets("Parameters").Range("F119").Value, Sheets("Parameters").Range("J120").Value, "B119")</SPAN></SPAN>
   
    'Niet productief - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F117").Value, Sheets("Parameters").Range("F118").Value, Sheets("Parameters").Range("F119").Value, Sheets("Parameters").Range("N120").Value, "B120")</SPAN></SPAN>
  
 
    'Algemeen - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F122").Value, Sheets("Parameters").Range("F123").Value, Sheets("Parameters").Range("F124").Value, Sheets("Parameters").Range("F125").Value, "B123")</SPAN></SPAN>
   
    'Algemeen - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F122").Value, Sheets("Parameters").Range("F123").Value, Sheets("Parameters").Range("F124").Value, Sheets("Parameters").Range("J125").Value, "B124")</SPAN></SPAN>
  
    'Algemeen - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F122").Value, Sheets("Parameters").Range("F123").Value, Sheets("Parameters").Range("F124").Value, Sheets("Parameters").Range("N125").Value, "B124")</SPAN></SPAN>
   
   
    'AH - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F127").Value, Sheets("Parameters").Range("F128").Value, Sheets("Parameters").Range("F129").Value, Sheets("Parameters").Range("F130").Value, "B128")</SPAN></SPAN>
   
    'AH - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F127").Value, Sheets("Parameters").Range("F128").Value, Sheets("Parameters").Range("F129").Value, Sheets("Parameters").Range("J130").Value, "B129")</SPAN></SPAN>
   
    'AH - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F127").Value, Sheets("Parameters").Range("F128").Value, Sheets("Parameters").Range("F129").Value, Sheets("Parameters").Range("N130").Value, "B130")</SPAN></SPAN>
  
   
    'AvG - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F132").Value, Sheets("Parameters").Range("F133").Value, Sheets("Parameters").Range("F134").Value, Sheets("Parameters").Range("F135").Value, "B133")</SPAN></SPAN>
   
    'AvG - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F132").Value, Sheets("Parameters").Range("F133").Value, Sheets("Parameters").Range("F134").Value, Sheets("Parameters").Range("J135").Value, "B134")</SPAN></SPAN>
   
    'AvG - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F132").Value, Sheets("Parameters").Range("F133").Value, Sheets("Parameters").Range("F134").Value, Sheets("Parameters").Range("N135").Value, "B135")</SPAN></SPAN>
 
 
    'ALB - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F137").Value, Sheets("Parameters").Range("F138").Value, Sheets("Parameters").Range("F139").Value, Sheets("Parameters").Range("F140").Value, "B138")</SPAN></SPAN>
   
    'ALB - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F137").Value, Sheets("Parameters").Range("F138").Value, Sheets("Parameters").Range("F139").Value, Sheets("Parameters").Range("J140").Value, "B139")</SPAN></SPAN>
   
    'ALB - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F137").Value, Sheets("Parameters").Range("F138").Value, Sheets("Parameters").Range("F139").Value, Sheets("Parameters").Range("N140").Value, "B140")</SPAN></SPAN>
   
 
    'Grand Total - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F142").Value, Sheets("Parameters").Range("F143").Value, Sheets("Parameters").Range("F144").Value, Sheets("Parameters").Range("F145").Value, "B143")</SPAN></SPAN>
 
    'Grand Total - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F142").Value, Sheets("Parameters").Range("F143").Value, Sheets("Parameters").Range("F144").Value, Sheets("Parameters").Range("J145").Value, "B144")</SPAN></SPAN>
   
    'Grand Total - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F142").Value, Sheets("Parameters").Range("F143").Value, Sheets("Parameters").Range("F144").Value, Sheets("Parameters").Range("N145").Value, "B145")</SPAN></SPAN>
 
 
    'Nieuw1 - Fixed:</SPAN></SPAN>
    '=========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F147").Value, Sheets("Parameters").Range("F148").Value, Sheets("Parameters").Range("F149").Value, Sheets("Parameters").Range("F150").Value, "B148")</SPAN></SPAN>
   
    'Nieuw1 - Variabel:</SPAN></SPAN>
    '============================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F147").Value, Sheets("Parameters").Range("F148").Value, Sheets("Parameters").Range("F149").Value, Sheets("Parameters").Range("J150").Value, "B149")</SPAN></SPAN>
   
    'Nieuw1 - Totaal:</SPAN></SPAN>
    '==========================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F147").Value, Sheets("Parameters").Range("F148").Value, Sheets("Parameters").Range("F149").Value, Sheets("Parameters").Range("N150").Value, "B149")</SPAN></SPAN>
 
   
    '====================================================================================</SPAN>
  
   
    'Urenstaat wordt weer gesloten</SPAN>
    '==============================</SPAN>
   
    Workbooks(Sheets("Parameters").Range("F25").Value).Close</SPAN></SPAN>
   
    '====================================================================================</SPAN>
   
   
    'Ophalen Tijdigheid WH (Proces Voortgang)</SPAN>
    '====================================================================================</SPAN>
   
    'Tijdigheid WH:</SPAN>
    '=================</SPAN>
   
    'Maandag:</SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("F110").Value, "B107")</SPAN></SPAN>
   
    'Dinsdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("F111").Value, "B108")</SPAN></SPAN>
   
    'Woensdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("F112").Value, "B109")</SPAN></SPAN>
   
    'Donderdag:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("L110").Value, "B110")</SPAN></SPAN>
   
    'Vrijdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("L111").Value, "B111")</SPAN></SPAN>
   
    'Zaterdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("L112").Value, "B112")</SPAN></SPAN>
   
    'Zondag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F107").Value, Sheets("Parameters").Range("F108").Value, Sheets("Parameters").Range("F109").Value, Sheets("Parameters").Range("O110").Value, "B113")</SPAN></SPAN>
       
       
    'Tijdigheid WH X-1:</SPAN>
    '===================</SPAN>
   
    'Maandag-1:</SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("F110").Value, "B99")</SPAN></SPAN>
   
    'Dinsdag-1:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("F111").Value, "B100")</SPAN></SPAN>
   
    'Woensdag-1:</SPAN></SPAN>
    '===========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("F112").Value, "B101")</SPAN></SPAN>
   
    'Donderdag-1:</SPAN></SPAN>
    '============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("L110").Value, "B102")</SPAN></SPAN>
   
    'Vrijdag-1:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("L111").Value, "B103")</SPAN></SPAN>
   
    'Zaterdag-1:</SPAN></SPAN>
    '===========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("L112").Value, "B104")</SPAN></SPAN>
   
    'Zondag-1:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T107").Value, Sheets("Parameters").Range("T108").Value, Sheets("Parameters").Range("T109").Value, Sheets("Parameters").Range("0110").Value, "B105")</SPAN></SPAN>
   
    '====================================================================================</SPAN>
   
   
    'Tijdigheid WH (CTP):</SPAN>
    '=====================</SPAN>
   
    'Maandag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("F276").Value, "B278")</SPAN></SPAN>
   
    'Dinsdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("F277").Value, "B279")</SPAN></SPAN>
   
    'Woensdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("F278").Value, "B280")</SPAN></SPAN>
   
    'Donderdag:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("L276").Value, "B281")</SPAN></SPAN>
   
    'Vrijdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("L277").Value, "B282")</SPAN></SPAN>
   
    'Zaterdag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("L278").Value, "B283")</SPAN></SPAN>
   
    'Zondag:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F273").Value, Sheets("Parameters").Range("F274").Value, Sheets("Parameters").Range("F275").Value, Sheets("Parameters").Range("O276").Value, "B284")</SPAN></SPAN>
   
    '====================================================================================</SPAN>
   
   
    'Tijdigheid WH (CTP) X-1:</SPAN>
    '========================</SPAN></SPAN>
   
    'Maandag-1:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("F276").Value, "B287")</SPAN></SPAN>
   
    'Dinsdag-1:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("F277").Value, "B288")</SPAN></SPAN>
   
    'Woensdag-1:</SPAN></SPAN>
    '===========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("F278").Value, "B289")</SPAN></SPAN>
   
    'Donderdag-1:</SPAN></SPAN>
    '============</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("L276").Value, "B290")</SPAN></SPAN>
   
    'Vrijdag-1:</SPAN></SPAN>
    '==========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("L277").Value, "B291")</SPAN></SPAN>
   
    'Zaterdag-1:</SPAN></SPAN>
    '===========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("L278").Value, "B292")</SPAN></SPAN>
   
    'Zondag-1:</SPAN></SPAN>
    '=========</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("T273").Value, Sheets("Parameters").Range("T274").Value, Sheets("Parameters").Range("T275").Value, Sheets("Parameters").Range("O276").Value, "B293")</SPAN></SPAN>
   
    '====================================================================================</SPAN>
   
   
 
    'Ophalen Personeel en Verzuim gegevens</SPAN>
    '====================================================================================</SPAN></SPAN>
   
    'Zieken:</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F157").Value, Sheets("Parameters").Range("F158").Value, Sheets("Parameters").Range("F159").Value, Sheets("Parameters").Range("F160").Value, "B158")</SPAN></SPAN>
   
    'Personeel:</SPAN></SPAN>
    '=================</SPAN></SPAN>
    Call GetData(Sheets("Parameters").Range("F162").Value, Sheets("Parameters").Range("F163").Value, Sheets("Parameters").Range("F164").Value, Sheets("Parameters").Range("F165").Value, "B163")</SPAN></SPAN>
   
    '====================================================================================</SPAN>
   
   
    'De Bezetting file in kwestie wordt geopend, zodat data sneller kan worden opgehaald.</SPAN>
    'De cellen F18 + F19 op Sheet "Parameters" worden gebruikt om de juiste Urenstaat te vinden.</SPAN>
    '============================================================================================</SPAN></SPAN>
   
    Workbooks.Open Filename:=Sheets("Parameters").Range("F18").Value & Sheets("Parameters").Range("F19").Value, UpdateLinks:=0, ReadOnly:=1, IgnoreReadOnlyRecommended:=1</SPAN></SPAN>
 
    Workbooks(Dashboard).Activate</SPAN>
   
   
    'Opslag per klant ophalen adhv Vlookup:</SPAN>
    '=======================================</SPAN>
   
    'Namen:</SPAN>
    '======</SPAN></SPAN>
   
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G185").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I185")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G186").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I186")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G187").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I187")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G188").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I188")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G189").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I189")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G190").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I190")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G191").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I191")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G192").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I192")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G193").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I193")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G194").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I194")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G195").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I195")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G196").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I196")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G197").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I197")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G198").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I198")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G199").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I199")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G200").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I200")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G201").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I201")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G202").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I202")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G203").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I203")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G204").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I204")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G205").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I205")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G206").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I206")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G207").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I207")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G208").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I208")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G209").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I209")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G210").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I210")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G211").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I211")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G212").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I212")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G213").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I213")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G214").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I214")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G215").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I215")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G216").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I216")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G217").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I217")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G218").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I218")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G219").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I219")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G220").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I220")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G221").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I221")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G222").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I222")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G223").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I223")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G224").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("S179").Value, "I224")</SPAN></SPAN>
   
  
    'Opslag:</SPAN></SPAN>
    '=======</SPAN></SPAN>
   
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G185").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K185")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G186").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K186")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G187").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K187")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G188").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K188")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G189").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K189")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G190").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K190")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G191").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K191")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G192").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K192")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G193").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K193")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G194").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K194")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G195").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K195")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G196").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K196")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G197").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K197")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G198").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K198")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G199").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K199")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G200").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K200")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G201").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K201")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G202").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K202")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G203").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K203")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G204").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K204")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G205").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K205")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G206").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K206")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G207").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K207")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G208").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K208")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G209").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K209")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G210").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K210")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G211").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K211")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G212").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K212")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G213").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K213")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G214").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K214")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G215").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K215")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G216").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K216")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G217").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K217")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G218").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K218")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G219").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K219")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G220").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K220")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G221").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K221")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G222").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K222")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G223").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K223")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F179").Value, Sheets("Parameters").Range("F180").Value, Sheets("Parameters").Range("F181").Value, Sheets("Parameters").Range("G224").Value, Sheets("Parameters").Range("L182").Value, Sheets("Parameters").Range("P182").Value, "K224")</SPAN></SPAN>
   
       
    'Bezetting file wordt weer gesloten</SPAN>
    '===================================</SPAN>
   
    Workbooks(Sheets("Parameters").Range("F19").Value).Close</SPAN></SPAN>
   
    '====================================================================================</SPAN>
   
   
    'Beladingsgraad Ahold ophalen adhv Vlookup:</SPAN>
    '===========================================</SPAN></SPAN>
   
    'Colli/RC:</SPAN></SPAN>
    '==========</SPAN></SPAN>
   
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H314").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J314")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H315").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J315")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H316").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J316")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H317").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J317")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H318").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J318")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H319").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J319")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H320").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J320")</SPAN></SPAN>
 
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H322").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J322")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H323").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J323")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H324").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J324")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H325").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J325")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H326").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J326")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H327").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J327")</SPAN></SPAN>
    Call Vlookup(Sheets("Parameters").Range("F307").Value, Sheets("Parameters").Range("F308").Value, Sheets("Parameters").Range("F309").Value, Sheets("Parameters").Range("H328").Value, Sheets("Parameters").Range("J310").Value, Sheets("Parameters").Range("N310").Value, "J328")</SPAN></SPAN>
 
       
End Sub</SPAN></SPAN>

 
       
Private Sub RemoveFormulas()</SPAN></SPAN>
 
 
    'Zet alle actieve formules om naar waarde's:</SPAN>
    '=============================================</SPAN>
   
    Call PasteValues("Parameters", "B18")   'Bezetting</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B24")   'Prestaties</SPAN></SPAN>
    Call PasteValues("Parameters", "B25")</SPAN></SPAN>
    Call PasteValues("Parameters", "B29")</SPAN></SPAN>
    Call PasteValues("Parameters", "B30")</SPAN></SPAN>
    Call PasteValues("Parameters", "B34")</SPAN></SPAN>
    Call PasteValues("Parameters", "B35")</SPAN></SPAN>
    Call PasteValues("Parameters", "B44")</SPAN></SPAN>
    Call PasteValues("Parameters", "B45")</SPAN></SPAN>
    Call PasteValues("Parameters", "B49")</SPAN></SPAN>
    Call PasteValues("Parameters", "B50")</SPAN></SPAN>
    Call PasteValues("Parameters", "B60")</SPAN></SPAN>
    Call PasteValues("Parameters", "B61")</SPAN></SPAN>
    Call PasteValues("Parameters", "B70")</SPAN></SPAN>
    Call PasteValues("Parameters", "B71")</SPAN></SPAN>
    Call PasteValues("Parameters", "B75")</SPAN></SPAN>
    Call PasteValues("Parameters", "B76")</SPAN></SPAN>
    Call PasteValues("Parameters", "B80")</SPAN></SPAN>
    Call PasteValues("Parameters", "B81")</SPAN></SPAN>
    Call PasteValues("Parameters", "B85")</SPAN></SPAN>
    Call PasteValues("Parameters", "B86")</SPAN></SPAN>
  
    Call PasteValues("Parameters", "S72")   'Prestaties Pallet IN Totals</SPAN></SPAN>
    Call PasteValues("Parameters", "S77")   'Prestaties Pallet OUT Totals</SPAN></SPAN>
    Call PasteValues("Parameters", "S87")   'Prestaties Rolcontainer Totals</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B107")  'Tijdigheid WH Ahold</SPAN></SPAN>
    Call PasteValues("Parameters", "B108")</SPAN></SPAN>
    Call PasteValues("Parameters", "B109")</SPAN></SPAN>
    Call PasteValues("Parameters", "B110")</SPAN></SPAN>
    Call PasteValues("Parameters", "B111")</SPAN></SPAN>
    Call PasteValues("Parameters", "B112")</SPAN></SPAN>
    Call PasteValues("Parameters", "B113")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B99")  'Tijdigheid WH Ahold X-1</SPAN></SPAN>
    Call PasteValues("Parameters", "B100")</SPAN></SPAN>
    Call PasteValues("Parameters", "B101")</SPAN></SPAN>
    Call PasteValues("Parameters", "B102")</SPAN></SPAN>
    Call PasteValues("Parameters", "B103")</SPAN></SPAN>
    Call PasteValues("Parameters", "B104")</SPAN></SPAN>
    Call PasteValues("Parameters", "B105")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B278")  'Tijdigheid CTP WH</SPAN></SPAN>
    Call PasteValues("Parameters", "B279")</SPAN></SPAN>
    Call PasteValues("Parameters", "B280")</SPAN></SPAN>
    Call PasteValues("Parameters", "B281")</SPAN></SPAN>
    Call PasteValues("Parameters", "B282")</SPAN></SPAN>
    Call PasteValues("Parameters", "B283")</SPAN></SPAN>
    Call PasteValues("Parameters", "B284")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B287")  'Tijdigheid CTP WH X-1</SPAN></SPAN>
    Call PasteValues("Parameters", "B288")</SPAN></SPAN>
    Call PasteValues("Parameters", "B289")</SPAN></SPAN>
    Call PasteValues("Parameters", "B290")</SPAN></SPAN>
    Call PasteValues("Parameters", "B291")</SPAN></SPAN>
    Call PasteValues("Parameters", "B292")</SPAN></SPAN>
    Call PasteValues("Parameters", "B293")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B118")  'Bestede uren</SPAN></SPAN>
    Call PasteValues("Parameters", "B119")</SPAN></SPAN>
    Call PasteValues("Parameters", "B120")</SPAN></SPAN>
    Call PasteValues("Parameters", "B123")</SPAN></SPAN>
    Call PasteValues("Parameters", "B124")</SPAN></SPAN>
    Call PasteValues("Parameters", "B125")</SPAN></SPAN>
    Call PasteValues("Parameters", "B128")</SPAN></SPAN>
    Call PasteValues("Parameters", "B129")</SPAN></SPAN>
    Call PasteValues("Parameters", "B130")</SPAN></SPAN>
    Call PasteValues("Parameters", "B133")</SPAN></SPAN>
    Call PasteValues("Parameters", "B134")</SPAN></SPAN>
    Call PasteValues("Parameters", "B135")</SPAN></SPAN>
    Call PasteValues("Parameters", "B138")</SPAN></SPAN>
    Call PasteValues("Parameters", "B139")</SPAN></SPAN>
    Call PasteValues("Parameters", "B140")</SPAN></SPAN>
    Call PasteValues("Parameters", "B143")</SPAN></SPAN>
    Call PasteValues("Parameters", "B144")</SPAN></SPAN>
    Call PasteValues("Parameters", "B145")</SPAN></SPAN>
    Call PasteValues("Parameters", "B148")</SPAN></SPAN>
    Call PasteValues("Parameters", "B149")</SPAN></SPAN>
    Call PasteValues("Parameters", "B150")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "B158")  'Ziekteverzuim</SPAN></SPAN>
    Call PasteValues("Parameters", "B163")  'Personeel</SPAN></SPAN>
   
    Call PasteValues("Parameters", "I185")  'Opslag per Klant (Namen)</SPAN></SPAN>
    Call PasteValues("Parameters", "I186")</SPAN></SPAN>
    Call PasteValues("Parameters", "I187")</SPAN></SPAN>
    Call PasteValues("Parameters", "I188")</SPAN></SPAN>
    Call PasteValues("Parameters", "I189")</SPAN></SPAN>
    Call PasteValues("Parameters", "I190")</SPAN></SPAN>
    Call PasteValues("Parameters", "I191")</SPAN></SPAN>
    Call PasteValues("Parameters", "I192")</SPAN></SPAN>
    Call PasteValues("Parameters", "I193")</SPAN></SPAN>
    Call PasteValues("Parameters", "I194")</SPAN></SPAN>
    Call PasteValues("Parameters", "I195")</SPAN></SPAN>
    Call PasteValues("Parameters", "I196")</SPAN></SPAN>
    Call PasteValues("Parameters", "I197")</SPAN></SPAN>
    Call PasteValues("Parameters", "I198")</SPAN></SPAN>
    Call PasteValues("Parameters", "I199")</SPAN></SPAN>
    Call PasteValues("Parameters", "I200")</SPAN></SPAN>
    Call PasteValues("Parameters", "I201")</SPAN></SPAN>
    Call PasteValues("Parameters", "I202")</SPAN></SPAN>
    Call PasteValues("Parameters", "I203")</SPAN></SPAN>
    Call PasteValues("Parameters", "I204")</SPAN></SPAN>
    Call PasteValues("Parameters", "I205")</SPAN></SPAN>
    Call PasteValues("Parameters", "I206")</SPAN></SPAN>
    Call PasteValues("Parameters", "I207")</SPAN></SPAN>
    Call PasteValues("Parameters", "I208")</SPAN></SPAN>
    Call PasteValues("Parameters", "I209")</SPAN></SPAN>
    Call PasteValues("Parameters", "I210")</SPAN></SPAN>
    Call PasteValues("Parameters", "I211")</SPAN></SPAN>
    Call PasteValues("Parameters", "I212")</SPAN></SPAN>
    Call PasteValues("Parameters", "I213")</SPAN></SPAN>
    Call PasteValues("Parameters", "I214")</SPAN></SPAN>
    Call PasteValues("Parameters", "I215")</SPAN></SPAN>
    Call PasteValues("Parameters", "I216")</SPAN></SPAN>
    Call PasteValues("Parameters", "I217")</SPAN></SPAN>
    Call PasteValues("Parameters", "I218")</SPAN></SPAN>
    Call PasteValues("Parameters", "I219")</SPAN></SPAN>
    Call PasteValues("Parameters", "I220")</SPAN></SPAN>
    Call PasteValues("Parameters", "I221")</SPAN></SPAN>
    Call PasteValues("Parameters", "I222")</SPAN></SPAN>
    Call PasteValues("Parameters", "I223")</SPAN></SPAN>
    Call PasteValues("Parameters", "I224")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "K185")  'Opslag per Klant (Opslag)</SPAN></SPAN>
    Call PasteValues("Parameters", "K186")</SPAN></SPAN>
    Call PasteValues("Parameters", "K187")</SPAN></SPAN>
    Call PasteValues("Parameters", "K188")</SPAN></SPAN>
    Call PasteValues("Parameters", "K189")</SPAN></SPAN>
    Call PasteValues("Parameters", "K190")</SPAN></SPAN>
    Call PasteValues("Parameters", "K191")</SPAN></SPAN>
    Call PasteValues("Parameters", "K192")</SPAN></SPAN>
    Call PasteValues("Parameters", "K193")</SPAN></SPAN>
    Call PasteValues("Parameters", "K194")</SPAN></SPAN>
    Call PasteValues("Parameters", "K195")</SPAN></SPAN>
    Call PasteValues("Parameters", "K196")</SPAN></SPAN>
    Call PasteValues("Parameters", "K197")</SPAN></SPAN>
    Call PasteValues("Parameters", "K198")</SPAN></SPAN>
    Call PasteValues("Parameters", "K199")</SPAN></SPAN>
    Call PasteValues("Parameters", "K200")</SPAN></SPAN>
    Call PasteValues("Parameters", "K201")</SPAN></SPAN>
    Call PasteValues("Parameters", "K202")</SPAN></SPAN>
    Call PasteValues("Parameters", "K203")</SPAN></SPAN>
    Call PasteValues("Parameters", "K204")</SPAN></SPAN>
    Call PasteValues("Parameters", "K205")</SPAN></SPAN>
    Call PasteValues("Parameters", "K206")</SPAN></SPAN>
    Call PasteValues("Parameters", "K207")</SPAN></SPAN>
    Call PasteValues("Parameters", "K208")</SPAN></SPAN>
    Call PasteValues("Parameters", "K209")</SPAN></SPAN>
    Call PasteValues("Parameters", "K210")</SPAN></SPAN>
    Call PasteValues("Parameters", "K211")</SPAN></SPAN>
    Call PasteValues("Parameters", "K212")</SPAN></SPAN>
    Call PasteValues("Parameters", "K213")</SPAN></SPAN>
    Call PasteValues("Parameters", "K214")</SPAN></SPAN>
    Call PasteValues("Parameters", "K215")</SPAN></SPAN>
    Call PasteValues("Parameters", "K216")</SPAN></SPAN>
    Call PasteValues("Parameters", "K217")</SPAN></SPAN>
    Call PasteValues("Parameters", "K218")</SPAN></SPAN>
    Call PasteValues("Parameters", "K219")</SPAN></SPAN>
    Call PasteValues("Parameters", "K220")</SPAN></SPAN>
    Call PasteValues("Parameters", "K221")</SPAN></SPAN>
    Call PasteValues("Parameters", "K222")</SPAN></SPAN>
    Call PasteValues("Parameters", "K223")</SPAN></SPAN>
    Call PasteValues("Parameters", "K224")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "J314")  'Beladingsgraad AH</SPAN></SPAN>
    Call PasteValues("Parameters", "J315")</SPAN></SPAN>
    Call PasteValues("Parameters", "J316")</SPAN></SPAN>
    Call PasteValues("Parameters", "J317")</SPAN></SPAN>
    Call PasteValues("Parameters", "J318")</SPAN></SPAN>
    Call PasteValues("Parameters", "J319")</SPAN></SPAN>
    Call PasteValues("Parameters", "J320")</SPAN></SPAN>
   
    Call PasteValues("Parameters", "J322")</SPAN></SPAN>
    Call PasteValues("Parameters", "J323")</SPAN></SPAN>
    Call PasteValues("Parameters", "J324")</SPAN></SPAN>
    Call PasteValues("Parameters", "J325")</SPAN></SPAN>
    Call PasteValues("Parameters", "J326")</SPAN></SPAN>
    Call PasteValues("Parameters", "J327")</SPAN></SPAN>
    Call PasteValues("Parameters", "J328")</SPAN>
 
End Sub</SPAN></SPAN>

 
 
 Private Sub Finish()</SPAN></SPAN>
   
    'End of routine</SPAN></SPAN>
    '===============</SPAN></SPAN>
 
    Application.CutCopyMode = False</SPAN></SPAN>
       
    'Plaats Datum + Tijd in Cell B13 (Last Updated Info):</SPAN></SPAN>
    '=================================================================</SPAN></SPAN>
 
    Sheets("Parameters").Range("B13").Formula = "=Now()"</SPAN></SPAN>
    Call PasteValues("Parameters", "B13")</SPAN></SPAN>
 
    '=================================================================</SPAN></SPAN>
   
    Sheets("Dashboard").Protect ("1234")</SPAN></SPAN>
       
    Sheets("Dashboard").Select</SPAN></SPAN>
    'Sheets("Dashboard").Range("K2").Select</SPAN></SPAN>
 
    Application.ScreenUpdating = True</SPAN></SPAN>
    Application.DisplayAlerts = True</SPAN></SPAN>
 
End Sub</SPAN></SPAN>

 
 
Function Dashboard() As String</SPAN></SPAN>
 
    Dashboard = ThisWorkbook.Name</SPAN></SPAN>
 
End Function</SPAN></SPAN>

 
Private Sub PasteValues(pvSheet, pvCell As String)</SPAN></SPAN>
 
    Sheets(pvSheet).Select</SPAN></SPAN>
    Sheets(pvSheet).Range(pvCell).Select</SPAN></SPAN>
    Selection.Copy</SPAN></SPAN>
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False</SPAN></SPAN>
 
End Sub</SPAN></SPAN>

 
Private Sub ValueToTargetCell(SourceSheet, SourceCell, TargetSheet, TargetCell As String)</SPAN></SPAN>
 
    Dim TargVal As String</SPAN></SPAN>
   
    Sheets(SourceSheet).Select</SPAN></SPAN>
    Range(SourceCell).Select</SPAN></SPAN>
    Selection.Copy</SPAN></SPAN>
    TargVal = Sheets(SourceSheet).Range(TargetCell).Value</SPAN></SPAN>
    Sheets(TargetSheet).Select</SPAN></SPAN>
    Range(TargVal).Select</SPAN></SPAN>
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False</SPAN></SPAN>
 
End Sub</SPAN></SPAN>

 
Private Sub Vlookup(strPath, strFile, strSheet, strCell, strTable, strColumn, strTarget)</SPAN></SPAN>
 
    strFormula = "=IF(ISNA(VLOOKUP(" & strCell & ",'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strTable & "," & strColumn & ",0)),"""",VLOOKUP(" & strCell & ",'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strTable & "," & strColumn & ",0))"</SPAN></SPAN>
   
    Sheets("Parameters").Select</SPAN></SPAN>
    Range(strTarget).Formula = strFormula</SPAN></SPAN>
   
 
End Sub</SPAN></SPAN>

 
Private Sub GetData(strPath, strFile, strSheet, strCell, strTarget)</SPAN></SPAN>
 
    strFormula = "='" & strPath & "[" & strFile & "]" & strSheet & "'!" & strCell</SPAN></SPAN>
   
    Sheets("Parameters").Select</SPAN></SPAN>
    Range(strTarget).Formula = strFormula</SPAN></SPAN>
   
   
End Sub</SPAN>

Jtufplata · Jun 28, 2013

Hi,

here are a few suggestions. you don't have to select a range to preform actions on it. If you do so, with this amount of data, it's kind of heavy.

for instance, try this:

Code:

Private Sub Vlookup(strPath, strFile, strSheet, strCell, strTable, strColumn, strTarget)
 
    strFormula = "=IF(ISNA(VLOOKUP(" & strCell & ",'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strTable & "," & strColumn & ",0)),"""",VLOOKUP(" & strCell & ",'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strTable & "," & strColumn & ",0))"
   
    'Sheets("Parameters").Select
    Sheets("Parameters").Range(strTarget).Formula = strFormula
   
 
End Sub


 
Private Sub GetData(strPath, strFile, strSheet, strCell, strTarget)
 
    strFormula = "='" & strPath & "[" & strFile & "]" & strSheet & "'!" & strCell
   
    'Sheets("Parameters").Select
    Sheets("Parameters").Range(strTarget).Formula = strFormula
   
   
End Sub

op
- same goes for the others .select -> selection."do stuff"

on the other hand, why do you need to hardcode the ranges when calling getdata, vlookup and pastedata? In my opinion, you could try to load all the information into an array, maybe even an array of arrays, to read/write the values. From experience, it is a lot faster, also, the code would be a lot easier to maintain, and more scalable (not sure if word exists =) ).

regards,
John

Teeroy · Jun 28, 2013

You can speed up converting formulae to values by (e.g. below) rather than multiple function calls to PasteValues that uses copy/pastespecial.

Code:

Range("I185:I224").formula = Range("I185:I224").value

NessPJ · Jun 28, 2013

Thanks for the input so far guys.

@ jtufplata:
The reason i use the 'select' method is because i have noticed in the past, not selecting the right sheet first results in actions being performed in the wrong area of the workbook. I just tried this again using your suggestions and noticed this 'issue' coming back once more.

@Teeroy:
This pushed the total runtime back to around 1 minute! Major difference...
To think that doing something as simple as: selecting a cell, copying it and pasting it back as a value would take up so much time when done a hundred times....
Thanks a lot!

Jtufplata · Jun 28, 2013

NessPJ said:
Thanks for the input so far guys.

@ jtufplata:
The reason i use the 'select' method is because i have noticed in the past, not selecting the right sheet first results in actions being performed in the wrong area of the workbook. I just tried this again using your suggestions and noticed this 'issue' coming back once more.

hum, maybe adding a workbook("wbname") prefix would solve it.

anyways, 1 min in 3,5 mins is a lot :D

NessPJ · Jun 28, 2013

Seems to work correctly no by adding the workbook name indeed.
Yet, not a single second was 'won' i'm affraid.

Jtufplata · Jun 28, 2013

hehe

you could also try the with/with end construct for the retrieve data sub:

Code:

Private Sub RetrieveData()
 
    On Error Resume Next
   
   
    'Variabelen definiëren:
    '======================
   
    Dim strPath As String
    Dim strFile As String
    Dim strSheet As String
    Dim strCell As String
    Dim strFormula As String
    Dim strTable As String
    Dim strColumn As String
    Dim strTarget As String
   
   
    strOffset = Sheets("Parameters").Range("S4").Value
  
   
    'Bezetting Hoogbouw
    '===================
   
    Call GetData(Sheets("Parameters").Range("F18").Value, Sheets("Parameters").Range("F19").Value, Sheets("Parameters").Range("F20").Value, Sheets("Parameters").Range("F21").Value, "B18")
   
 
    'Ophalen prestaties + normen:
    '====================================================================================
   
   
    'Urenstaat in kwestie wordt geopend, zodat data sneller kan worden opgehaald.
    'De cellen F24 + F25 op Sheet "Parameters" worden gebruikt om de juiste Urenstaat te vinden.
    '============================================================================================
   
    Workbooks.Open Filename:=Sheets("Parameters").Range("F24").Value & Sheets("Parameters").Range("F25").Value, UpdateLinks:=0, ReadOnly:=1, IgnoreReadOnlyRecommended:=1
 
    Workbooks(Dashboard).Activate
   
   With Sheets("Parameters")
    'AH Picking rate
    '================
    Call GetData(.Range("F24").Value, .Range("F25").Value, .Range("F26").Value, .Range("F27").Value, "B24")
   
    'AH Picking norm
    '================
    Call GetData(.Range("F24").Value, .Range("F25").Value, .Range("F26").Value, .Range("L27").Value, "B25")
 
   
    'AvG Picking rate
    '=================
    Call GetData(.Range("F29").Value, .Range("F30").Value, .Range("F31").Value, .Range("F32").Value, "B29")
   
    'AvG Picking norm
    '=================
    Call GetData(.Range("F29").Value, .Range("F30").Value, .Range("F31").Value, .Range("L32").Value, "B30")
   
   
    'ALB Picking rate
    '=================
    Call GetData(.Range("F34").Value, .Range("F35").Value, .Range("F36").Value, .Range("F37").Value, "B34")
   
    'ALB Picking norm
    '=================
    Call GetData(.Range("F34").Value, .Range("F35").Value, .Range("F36").Value, .Range("L37").Value, "B35")
 
   
    'Replenishment rate
    '===================
    Call GetData(.Range("F44").Value, .Range("F45").Value, .Range("F46").Value, .Range("F47").Value, "B44")
   
    'Replenishment norm
    '===================
    Call GetData(.Range("F44").Value, .Range("F45").Value, .Range("F46").Value, .Range("L47").Value, "B45")
 
   
    'ALB Replenishment rate
    '=======================
    Call GetData(.Range("F49").Value, .Range("F50").Value, .Range("F51").Value, .Range("F52").Value, "B49")
   
    'ALB Replenishment norm
    '=======================
    Call GetData(.Range("F49").Value, .Range("F50").Value, .Range("F51").Value, .Range("L52").Value, "B50")
 
   
    'ALB Assembly rate
    '==================
    Call GetData(.Range("F60").Value, .Range("F61").Value, .Range("F62").Value, .Range("F63").Value, "B60")
   
    'ALB Assembly norm
    '==================
    Call GetData(.Range("F60").Value, .Range("F61").Value, .Range("F62").Value, .Range("L63").Value, "B61")
 
   
    'Pallets IN Rate
    '=================
    Call GetData(.Range("F70").Value, .Range("F71").Value, .Range("F72").Value, .Range("F73").Value, "B70")
   
    'Pallets IN Norm
    '==================
    Call GetData(.Range("F70").Value, .Range("F71").Value, .Range("F72").Value, .Range("L73").Value, "B71")
   
    'Pallets IN Total
    '==================
    Call GetData(.Range("F70").Value, .Range("F71").Value, .Range("F72").Value, .Range("S71").Value, "S72")
 
   
   
    'Pallets OUT Rate
    '=================
    Call GetData(.Range("F75").Value, .Range("F76").Value, .Range("F77").Value, .Range("F78").Value, "B75")
   
    'Pallets OUT Norm
    '==================
    Call GetData(.Range("F75").Value, .Range("F76").Value, .Range("F77").Value, .Range("L78").Value, "B76")
   
    'Pallets OUT Total
    '==================
    Call GetData(.Range("F75").Value, .Range("F76").Value, .Range("F77").Value, .Range("S76").Value, "S77")
 
   
    'Rolc. IN Rate
    '==============
    Call GetData(.Range("F80").Value, .Range("F81").Value, .Range("F82").Value, .Range("F83").Value, "B80")
 
    'Rolc. IN Norm
    '==============
    Call GetData(.Range("F80").Value, .Range("F81").Value, .Range("F82").Value, .Range("L83").Value, "B81")
   
   
    'Rolc. OUT Rate
    '===============
    Call GetData(.Range("F85").Value, .Range("F86").Value, .Range("F87").Value, .Range("F88").Value, "B85")
   
    'Rolc. OUT Norm
    '===============
    Call GetData(.Range("F85").Value, .Range("F86").Value, .Range("F87").Value, .Range("L88").Value, "B86")
 
    'Rolc. OUT Total
    '===============
    Call GetData(.Range("F85").Value, .Range("F86").Value, .Range("F87").Value, .Range("S86").Value, "S87")
 
   
    '====================================================================================
   
   
    'Ophalen uren (urenstaat)
    '====================================================================================
 
    'Niet productief - Fixed:
    '=========================
    Call GetData(.Range("F117").Value, .Range("F118").Value, .Range("F119").Value, .Range("F120").Value, "B118")
   
    'Niet productief - Variabel:
    '============================
    Call GetData(.Range("F117").Value, .Range("F118").Value, .Range("F119").Value, .Range("J120").Value, "B119")
   
    'Niet productief - Totaal:
    '==========================
    Call GetData(.Range("F117").Value, .Range("F118").Value, .Range("F119").Value, .Range("N120").Value, "B120")
  
 
    'Algemeen - Fixed:
    '=========================
    Call GetData(.Range("F122").Value, .Range("F123").Value, .Range("F124").Value, .Range("F125").Value, "B123")
   
    'Algemeen - Variabel:
    '============================
    Call GetData(.Range("F122").Value, .Range("F123").Value, .Range("F124").Value, .Range("J125").Value, "B124")
  
    'Algemeen - Totaal:
    '==========================
    Call GetData(.Range("F122").Value, .Range("F123").Value, .Range("F124").Value, .Range("N125").Value, "B124")
   
   
    'AH - Fixed:
    '=========================
    Call GetData(.Range("F127").Value, .Range("F128").Value, .Range("F129").Value, .Range("F130").Value, "B128")
   
    'AH - Variabel:
    '============================
    Call GetData(.Range("F127").Value, .Range("F128").Value, .Range("F129").Value, .Range("J130").Value, "B129")
   
    'AH - Totaal:
    '==========================
    Call GetData(.Range("F127").Value, .Range("F128").Value, .Range("F129").Value, .Range("N130").Value, "B130")
  
   
    'AvG - Fixed:
    '=========================
    Call GetData(.Range("F132").Value, .Range("F133").Value, .Range("F134").Value, .Range("F135").Value, "B133")
   
    'AvG - Variabel:
    '============================
    Call GetData(.Range("F132").Value, .Range("F133").Value, .Range("F134").Value, .Range("J135").Value, "B134")
   
    'AvG - Totaal:
    '==========================
    Call GetData(.Range("F132").Value, .Range("F133").Value, .Range("F134").Value, .Range("N135").Value, "B135")
 
 
    'ALB - Fixed:
    '=========================
    Call GetData(.Range("F137").Value, .Range("F138").Value, .Range("F139").Value, .Range("F140").Value, "B138")
   
    'ALB - Variabel:
    '============================
    Call GetData(.Range("F137").Value, .Range("F138").Value, .Range("F139").Value, .Range("J140").Value, "B139")
   
    'ALB - Totaal:
    '==========================
    Call GetData(.Range("F137").Value, .Range("F138").Value, .Range("F139").Value, .Range("N140").Value, "B140")
   
 
    'Grand Total - Fixed:
    '=========================
    Call GetData(.Range("F142").Value, .Range("F143").Value, .Range("F144").Value, .Range("F145").Value, "B143")
 
    'Grand Total - Variabel:
    '============================
    Call GetData(.Range("F142").Value, .Range("F143").Value, .Range("F144").Value, .Range("J145").Value, "B144")
   
    'Grand Total - Totaal:
    '==========================
    Call GetData(.Range("F142").Value, .Range("F143").Value, .Range("F144").Value, .Range("N145").Value, "B145")
 
 
    'Nieuw1 - Fixed:
    '=========================
    Call GetData(.Range("F147").Value, .Range("F148").Value, .Range("F149").Value, .Range("F150").Value, "B148")
   
    'Nieuw1 - Variabel:
    '============================
    Call GetData(.Range("F147").Value, .Range("F148").Value, .Range("F149").Value, .Range("J150").Value, "B149")
   
    'Nieuw1 - Totaal:
    '==========================
    Call GetData(.Range("F147").Value, .Range("F148").Value, .Range("F149").Value, .Range("N150").Value, "B149")
 
   
    '====================================================================================
  
   
    'Urenstaat wordt weer gesloten
    '==============================
   
    Workbooks(.Range("F25").Value).Close
   
    '====================================================================================
   
   
    'Ophalen Tijdigheid WH (Proces Voortgang)
    '====================================================================================
   
    'Tijdigheid WH:
    '=================
   
    'Maandag:
    '=========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("F110").Value, "B107")
   
    'Dinsdag:
    '=========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("F111").Value, "B108")
   
    'Woensdag:
    '=========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("F112").Value, "B109")
   
    'Donderdag:
    '==========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("L110").Value, "B110")
   
    'Vrijdag:
    '=========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("L111").Value, "B111")
   
    'Zaterdag:
    '=========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("L112").Value, "B112")
   
    'Zondag:
    '=========
    Call GetData(.Range("F107").Value, .Range("F108").Value, .Range("F109").Value, .Range("O110").Value, "B113")
       
       
    'Tijdigheid WH X-1:
    '===================
   
    'Maandag-1:
    '==========
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("F110").Value, "B99")
   
    'Dinsdag-1:
    '==========
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("F111").Value, "B100")
   
    'Woensdag-1:
    '===========
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("F112").Value, "B101")
   
    'Donderdag-1:
    '============
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("L110").Value, "B102")
   
    'Vrijdag-1:
    '==========
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("L111").Value, "B103")
   
    'Zaterdag-1:
    '===========
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("L112").Value, "B104")
   
    'Zondag-1:
    '=========
    Call GetData(.Range("T107").Value, .Range("T108").Value, .Range("T109").Value, .Range("0110").Value, "B105")
   
    '====================================================================================
   
   
    'Tijdigheid WH (CTP):
    '=====================
   
    'Maandag:
    '=========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("F276").Value, "B278")
   
    'Dinsdag:
    '=========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("F277").Value, "B279")
   
    'Woensdag:
    '=========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("F278").Value, "B280")
   
    'Donderdag:
    '==========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("L276").Value, "B281")
   
    'Vrijdag:
    '=========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("L277").Value, "B282")
   
    'Zaterdag:
    '=========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("L278").Value, "B283")
   
    'Zondag:
    '=========
    Call GetData(.Range("F273").Value, .Range("F274").Value, .Range("F275").Value, .Range("O276").Value, "B284")
   
    '====================================================================================
   
   
    'Tijdigheid WH (CTP) X-1:
    '========================
   
    'Maandag-1:
    '==========
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("F276").Value, "B287")
   
    'Dinsdag-1:
    '==========
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("F277").Value, "B288")
   
    'Woensdag-1:
    '===========
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("F278").Value, "B289")
   
    'Donderdag-1:
    '============
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("L276").Value, "B290")
   
    'Vrijdag-1:
    '==========
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("L277").Value, "B291")
   
    'Zaterdag-1:
    '===========
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("L278").Value, "B292")
   
    'Zondag-1:
    '=========
    Call GetData(.Range("T273").Value, .Range("T274").Value, .Range("T275").Value, .Range("O276").Value, "B293")
   
    '====================================================================================
   
   
 
    'Ophalen Personeel en Verzuim gegevens
    '====================================================================================
   
    'Zieken:
    '=================
    Call GetData(.Range("F157").Value, .Range("F158").Value, .Range("F159").Value, .Range("F160").Value, "B158")
   
    'Personeel:
    '=================
    Call GetData(.Range("F162").Value, .Range("F163").Value, .Range("F164").Value, .Range("F165").Value, "B163")
   
    '====================================================================================
   
   
    'De Bezetting file in kwestie wordt geopend, zodat data sneller kan worden opgehaald.
    'De cellen F18 + F19 op Sheet "Parameters" worden gebruikt om de juiste Urenstaat te vinden.
    '============================================================================================
   
    Workbooks.Open Filename:=.Range("F18").Value & .Range("F19").Value, UpdateLinks:=0, ReadOnly:=1, IgnoreReadOnlyRecommended:=1
 
    Workbooks(Dashboard).Activate
   
   
    'Opslag per klant ophalen adhv Vlookup:
    '=======================================
   
    'Namen:
    '======
   
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G185").Value, .Range("L182").Value, .Range("S179").Value, "I185")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G186").Value, .Range("L182").Value, .Range("S179").Value, "I186")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G187").Value, .Range("L182").Value, .Range("S179").Value, "I187")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G188").Value, .Range("L182").Value, .Range("S179").Value, "I188")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G189").Value, .Range("L182").Value, .Range("S179").Value, "I189")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G190").Value, .Range("L182").Value, .Range("S179").Value, "I190")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G191").Value, .Range("L182").Value, .Range("S179").Value, "I191")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G192").Value, .Range("L182").Value, .Range("S179").Value, "I192")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G193").Value, .Range("L182").Value, .Range("S179").Value, "I193")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G194").Value, .Range("L182").Value, .Range("S179").Value, "I194")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G195").Value, .Range("L182").Value, .Range("S179").Value, "I195")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G196").Value, .Range("L182").Value, .Range("S179").Value, "I196")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G197").Value, .Range("L182").Value, .Range("S179").Value, "I197")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G198").Value, .Range("L182").Value, .Range("S179").Value, "I198")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G199").Value, .Range("L182").Value, .Range("S179").Value, "I199")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G200").Value, .Range("L182").Value, .Range("S179").Value, "I200")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G201").Value, .Range("L182").Value, .Range("S179").Value, "I201")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G202").Value, .Range("L182").Value, .Range("S179").Value, "I202")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G203").Value, .Range("L182").Value, .Range("S179").Value, "I203")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G204").Value, .Range("L182").Value, .Range("S179").Value, "I204")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G205").Value, .Range("L182").Value, .Range("S179").Value, "I205")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G206").Value, .Range("L182").Value, .Range("S179").Value, "I206")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G207").Value, .Range("L182").Value, .Range("S179").Value, "I207")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G208").Value, .Range("L182").Value, .Range("S179").Value, "I208")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G209").Value, .Range("L182").Value, .Range("S179").Value, "I209")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G210").Value, .Range("L182").Value, .Range("S179").Value, "I210")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G211").Value, .Range("L182").Value, .Range("S179").Value, "I211")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G212").Value, .Range("L182").Value, .Range("S179").Value, "I212")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G213").Value, .Range("L182").Value, .Range("S179").Value, "I213")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G214").Value, .Range("L182").Value, .Range("S179").Value, "I214")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G215").Value, .Range("L182").Value, .Range("S179").Value, "I215")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G216").Value, .Range("L182").Value, .Range("S179").Value, "I216")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G217").Value, .Range("L182").Value, .Range("S179").Value, "I217")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G218").Value, .Range("L182").Value, .Range("S179").Value, "I218")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G219").Value, .Range("L182").Value, .Range("S179").Value, "I219")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G220").Value, .Range("L182").Value, .Range("S179").Value, "I220")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G221").Value, .Range("L182").Value, .Range("S179").Value, "I221")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G222").Value, .Range("L182").Value, .Range("S179").Value, "I222")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G223").Value, .Range("L182").Value, .Range("S179").Value, "I223")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G224").Value, .Range("L182").Value, .Range("S179").Value, "I224")
   
  
    'Opslag:
    '=======
   
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G185").Value, .Range("L182").Value, .Range("P182").Value, "K185")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G186").Value, .Range("L182").Value, .Range("P182").Value, "K186")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G187").Value, .Range("L182").Value, .Range("P182").Value, "K187")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G188").Value, .Range("L182").Value, .Range("P182").Value, "K188")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G189").Value, .Range("L182").Value, .Range("P182").Value, "K189")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G190").Value, .Range("L182").Value, .Range("P182").Value, "K190")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G191").Value, .Range("L182").Value, .Range("P182").Value, "K191")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G192").Value, .Range("L182").Value, .Range("P182").Value, "K192")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G193").Value, .Range("L182").Value, .Range("P182").Value, "K193")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G194").Value, .Range("L182").Value, .Range("P182").Value, "K194")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G195").Value, .Range("L182").Value, .Range("P182").Value, "K195")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G196").Value, .Range("L182").Value, .Range("P182").Value, "K196")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G197").Value, .Range("L182").Value, .Range("P182").Value, "K197")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G198").Value, .Range("L182").Value, .Range("P182").Value, "K198")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G199").Value, .Range("L182").Value, .Range("P182").Value, "K199")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G200").Value, .Range("L182").Value, .Range("P182").Value, "K200")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G201").Value, .Range("L182").Value, .Range("P182").Value, "K201")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G202").Value, .Range("L182").Value, .Range("P182").Value, "K202")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G203").Value, .Range("L182").Value, .Range("P182").Value, "K203")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G204").Value, .Range("L182").Value, .Range("P182").Value, "K204")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G205").Value, .Range("L182").Value, .Range("P182").Value, "K205")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G206").Value, .Range("L182").Value, .Range("P182").Value, "K206")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G207").Value, .Range("L182").Value, .Range("P182").Value, "K207")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G208").Value, .Range("L182").Value, .Range("P182").Value, "K208")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G209").Value, .Range("L182").Value, .Range("P182").Value, "K209")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G210").Value, .Range("L182").Value, .Range("P182").Value, "K210")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G211").Value, .Range("L182").Value, .Range("P182").Value, "K211")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G212").Value, .Range("L182").Value, .Range("P182").Value, "K212")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G213").Value, .Range("L182").Value, .Range("P182").Value, "K213")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G214").Value, .Range("L182").Value, .Range("P182").Value, "K214")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G215").Value, .Range("L182").Value, .Range("P182").Value, "K215")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G216").Value, .Range("L182").Value, .Range("P182").Value, "K216")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G217").Value, .Range("L182").Value, .Range("P182").Value, "K217")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G218").Value, .Range("L182").Value, .Range("P182").Value, "K218")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G219").Value, .Range("L182").Value, .Range("P182").Value, "K219")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G220").Value, .Range("L182").Value, .Range("P182").Value, "K220")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G221").Value, .Range("L182").Value, .Range("P182").Value, "K221")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G222").Value, .Range("L182").Value, .Range("P182").Value, "K222")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G223").Value, .Range("L182").Value, .Range("P182").Value, "K223")
    Call Vlookup(.Range("F179").Value, .Range("F180").Value, .Range("F181").Value, .Range("G224").Value, .Range("L182").Value, .Range("P182").Value, "K224")
   
       
    'Bezetting file wordt weer gesloten
    '===================================
   
    Workbooks(.Range("F19").Value).Close
   
    '====================================================================================
   
   
    'Beladingsgraad Ahold ophalen adhv Vlookup:
    '===========================================
   
    'Colli/RC:
    '==========
   
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H314").Value, .Range("J310").Value, .Range("N310").Value, "J314")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H315").Value, .Range("J310").Value, .Range("N310").Value, "J315")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H316").Value, .Range("J310").Value, .Range("N310").Value, "J316")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H317").Value, .Range("J310").Value, .Range("N310").Value, "J317")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H318").Value, .Range("J310").Value, .Range("N310").Value, "J318")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H319").Value, .Range("J310").Value, .Range("N310").Value, "J319")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H320").Value, .Range("J310").Value, .Range("N310").Value, "J320")
 
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H322").Value, .Range("J310").Value, .Range("N310").Value, "J322")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H323").Value, .Range("J310").Value, .Range("N310").Value, "J323")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H324").Value, .Range("J310").Value, .Range("N310").Value, "J324")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H325").Value, .Range("J310").Value, .Range("N310").Value, "J325")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H326").Value, .Range("J310").Value, .Range("N310").Value, "J326")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H327").Value, .Range("J310").Value, .Range("N310").Value, "J327")
    Call Vlookup(.Range("F307").Value, .Range("F308").Value, .Range("F309").Value, .Range("H328").Value, .Range("J310").Value, .Range("N310").Value, "J328")
 End With
       
End Sub

Teeroy · Jun 28, 2013

I was about to suggest the With block, the same as Jtufplata. It will probably not change runtime much but the coding is clearer.

The GetData call always seems to pass data in the same relative position. To minimise your code you could just pass the first Range and the GetData sub could collect all the data relative to that using offset.

I would strongly suggest that you rename the Vlookup routine. It is confusing and generally a bad idea to name a sub the same as an inbuilt function.

I very rarely use select or activate (my normal exception is using pastespecial on a different sheet to the Activesheet). The issue that you mention, working on the wrong sheet, usually occurs where a Range has not been referenced to a sheet so it defaults to ActiveSheet as the parent object.
e.g.

Code:

Sheets("Parameters").Select
Range(strTarget).Formula = strFormula

is better written fully referenced as

Code:

Sheets("Parameters").Range(strTarget).Formula = strFormula

I hope this helps.

NessPJ · Jul 3, 2013

@ Jtufplata + Teeroy:

Thanks a lot for your input and information guys.
I will try and make the best of it. When i was three quarters of the way in the process of creating this workbook/dashboard i realised it would have been much cleaner and easier to have used some sort of database or array style for my data indeed.
Alas, doing this would mean recreating pretty much the entire sheet i use for my current parameters. Perhaps on a rainy day....

I will still implement all tips for shortening/cleaning up routines.

Optimize/speed-up my large VBA routine for obtaining data?

NessPJ

Active Member

Excel Facts

Jtufplata

Board Regular

Teeroy

Well-known Member

NessPJ

Active Member

Jtufplata

Board Regular

NessPJ

Active Member

Jtufplata

Board Regular

Teeroy

Well-known Member

NessPJ

Active Member

Similar threads

Forum statistics

Share this page

Optimize/speed-up my large VBA routine for obtaining data?

Active Member

Excel Facts

Board Regular

Well-known Member

Active Member

Board Regular

Active Member

Board Regular

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock