Hi all I am recently trying to code a vba macro to retrieve data from multiple sheets with different formats and paste them together in one masterlist. With a relatively large amount of data involved, I have decided to use arrays to store data i retrieved from other workbooks.
Here is a copy of the array codes involved:
Code
Sub mainRetrieval()
Dim updateWbook, intelWbook As Workbook
Dim intelSht, updateSht As Worksheet
Dim ctyName, shtName, wbName As String
Dim confirmationRetrieve, i, dirNo As Long
Dim wbDirArray(15) As String
Dim wbNameArray(15) As String
Dim shtNameArray(15) As String
Dim ctyNameArray(15) As String
'comfirmation box
confirmationRetrieve = MsgBox("Do you want to retrieve data?", vbYesNo + vbInformation, "Retrieve Confirmation")
If confirmationRetrieve = vbYes Then
GoTo retrieveData
Else
Exit Sub
End If
retrieveData:
Set updateWbook = Workbooks("FO CDB Compiler 2.xlsm")
Set updateSht = updateWbook.Sheets("Compiled Data")
'clearCDBworksheet
Call clearImportworksheet(updateSht)
'Storing values in the array
wbDirArray(0) = 12 'Month-Year
wbDirArray(1) = 12 'West Arbs
wbDirArray(2) = 12 'Kuwait
wbDirArray(3) = 12 'Saudi
wbDirArray(4) = 12 'Other Mideast
wbDirArray(5) = 12 'India Arrival
wbDirArray(6) = 12 'India Loading
wbDirArray(7) = 12 'Total Mideast
wbDirArray(8) = 12 'Iran
wbDirArray(9) = 12 'UAE
wbDirArray(10) = 12 'Total Asia
wbDirArray(11) = 12 'Japan
wbDirArray(12) = 12 'South Korea
wbDirArray(13) = 12 'Taiwan
wbDirArray(14) = 12 'Thailand
wbDirArray(15) = 12 'Other Asia
wbNameArray(0) = "Test Copy Fuel Oil Intel (Arbs) 2016.xlsm" 'trying with test copies first
wbNameArray(1) = "Test Copy Fuel Oil Intel (Arbs) 2016.xlsm"
wbNameArray(2) = "Test Copy Fuel Oil Intel (Mideast) 2016.xlsm"
wbNameArray(3) = "Test Copy Fuel Oil Intel (Mideast) 2016.xlsm"
wbNameArray(4) = "Test Copy Fuel Oil Intel (Mideast) 2016.xlsm"
wbNameArray(5) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(6) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(7) = "Test Copy Fuel Oil Intel (Mideast) 2016.xlsm"
wbNameArray(8) = "Test Copy Fuel Oil Intel (Mideast) 2016.xlsm"
wbNameArray(9) = "Test Copy Fuel Oil Intel (Mideast) 2016.xlsm"
wbNameArray(10) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(11) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(12) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(13) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(14) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
wbNameArray(15) = "Test Copy Fuel Oil Intel (Asia) 2016.xlsm"
shtNameArray(0) = "NewTestSummaryList"
shtNameArray(1) = "NewTestSummaryList"
shtNameArray(2) = "NewTestSummaryList"
shtNameArray(3) = "NewTestSummaryList"
shtNameArray(4) = "NewTestSummaryList"
shtNameArray(5) = "NewTestSummaryList"
shtNameArray(6) = "NewTestSummaryList"
shtNameArray(7) = "NewTestSummaryList"
shtNameArray(8) = "NewTestSummaryList"
shtNameArray(9) = "NewTestSummaryList"
shtNameArray(10) = "NewTestSummaryList"
shtNameArray(11) = "NewTestSummaryList"
shtNameArray(12) = "NewTestSummaryList"
shtNameArray(13) = "NewTestSummaryList"
shtNameArray(14) = "NewTestSummaryList"
shtNameArray(15) = "NewTestSummaryList"
ctyNameArray(0) = "Month"
ctyNameArray(1) = "West Arbs Total"
ctyNameArray(2) = "Kuwait"
ctyNameArray(3) = "Saudi"
ctyNameArray(4) = "Iraq and Other ME"
ctyNameArray(5) = "India Arrival"
ctyNameArray(6) = "India Loading" 'values not confirmed
ctyNameArray(7) = "Total Mideast"
ctyNameArray(8) = "Iran"
ctyNameArray(9) = "UAE"
ctyNameArray(10) = "Total Asia"
ctyNameArray(11) = "Japan"
ctyNameArray(12) = "South Korea"
ctyNameArray(13) = "Taiwan"
ctyNameArray(14) = "Thailand"
ctyNameArray(15) = "Other Asia" 'values not confirmed
'optimise macro
Application.ScreenUpdating = False
For i = 0 To UBound(wbDirArray)
dirNo = wbDirArray(i)
wbName = wbNameArray(i)
shtName = shtNameArray(i)
ctyName = ctyNameArray(i)
Call openWorkbook(dirNo)
Set intelWbook = Workbooks(wbName)
Set intelSht = intelWbook.Sheets(shtName)
'retrieve data and close workbook after retrieving
Call retreiveData(intelSht, updateSht, ctyName, i)
Call closeWorkbook(intelWbook)
Next i
Application.ScreenUpdating = True
'message box and unhighlight update sheet
MsgBox ("Data retrieved successfully.")
updateSht.Activate
End SubHowever I am encountering errors from a particular line of code "Set intelWbook = Workbooks(wbName)" which has the error 9 out of range error. Please help!
