Anigito
New Member
- Joined
- Jul 16, 2011
- Messages
- 17
- Office Version
- 2019
- Platform
- Windows
Version: MS Excel 2019
All macros are in ThisWorkbook location.
Macros are activated by buttons.
Although the macros are available through VBA viewer, I cannot select and link the macro to the button in the new file (the list is empty; see images attached).
What does the macro do: The macro opens worksheets one by one from the list defined as Array of names (as Variable), uses the data from to populate range with values in selected sheet.
I have ran code with debugger. The code runs through number of sheets, doing, what it is supposed to, and then on one of the sheets it fails with an error (see subject).
I understand, that this is caused by system settings, rather, than code itself. Please advise corrective actions.
All macros are in ThisWorkbook location.
Macros are activated by buttons.
Although the macros are available through VBA viewer, I cannot select and link the macro to the button in the new file (the list is empty; see images attached).
What does the macro do: The macro opens worksheets one by one from the list defined as Array of names (as Variable), uses the data from to populate range with values in selected sheet.
I have ran code with debugger. The code runs through number of sheets, doing, what it is supposed to, and then on one of the sheets it fails with an error (see subject).
I understand, that this is caused by system settings, rather, than code itself. Please advise corrective actions.
VBA Code:
Code (the very same code works on different file without errors):
Sub macroname()
' Populate table from another file (data formatted as table from Access db)
' using conditions: Sheetname (as cABBRs), Model.Suffix (as Model.Suffix), WeekNumber
Dim wARR As Variant
Dim EndRow, i, j, k As Long
Dim skuLst, abrLst, wkLst, soLst, whLst, sdLst As Range
Dim raw As Worksheet
Dim rawO As ListObject
Start:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
EndRow = Cells(Rows.Count, 4).End(xlUp).Row
Set raw = ThisWorkbook.Worksheets("IO") ' source data rests here
Set rawO = raw.ListObjects("tbl") ' source data table -- did this because of the reply above, still doesn't work
'Refrech Access DB data
rawO.QueryTable.Refresh BackgroundQuery:=False
Set abrLst = raw.Range("$C:$C")
Set skuLst = raw.Range("$D:$D")
Set wkLst = raw.Range("$A:$A")
Set soLst = raw.Range("$E:$E")
Set whLst = raw.Range("$F:$F")
Set sdLst = raw.Range("$G:$G")
For Each wARR In Array("A", "B", "C", "D", "E", "F") 'Array of KAs by sheet names
For j = 0 To 3 ' Populate data Part I
For i = 26 To EndRow
With ThisWorkbook.Worksheets(wARR)
.Cells(i, 11 + j) = Application.WorksheetFunction.SumIfs(whLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 11 + j)) - _
Application.WorksheetFunction.SumIfs(sdLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 11 + j))
End With
Next i
Next j
For k = 0 To 7 ' Populate data Part II
For i = 26 To EndRow
With ThisWorkbook.Worksheets(wARR)
.Cells(i, 46 + k) = Application.WorksheetFunction.SumIfs(soLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 46 + k))
End With
Next i
Next k
For i = 26 To EndRow ' Populate data Part III
With ThisWorkbook.Worksheets(wARR)
.Cells(i, 1) = Application.WorksheetFunction.SumIfs(sdLst, abrLst, .Range("A1").Value, skuLst, .Range("D" & i).Value, wkLst, .Cells(25, 14))
End With
Next i
Next wARR
ThisWorkbook.Worksheets("SMRY").Range("D3") = Format(Now(), "dd/mm/yyyy HH:MM") ' Set Timestamp
Set EndRow = Nothing
Set i = Nothing
Set j = Nothing
Set skuLst = Nothing
Set abrLst = Nothing
Set wkLst = Nothing
Set soLst = Nothing
Set whLst = Nothing
Set sdLst = Nothing
Set wARR = Nothing
Set raw = Nothing
Set rawO = Nothing
Finish:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub