I would like to create a macro function that allows you to collect data from many Excel files (Workbooks) into one a summary statement for a specific stage of implementation and deadline in another file.
All those files will be collected in one folder (also this summary file).
I would like to choose the implementation stage and deadline by manually entering (input box) or selecting from the drop-down list of available options after running the macro function (eg. Phase1, term 40 – like on screenshot) and should be supported if another phase is added.
After going through all the files in the above-mentioned folder (completing the data download), macro function needs to sort this data by name or index and calculate the total amount of demand (sum of quantities). (select and calculate values for unique items) but before that it need to sort it in such a way that only the main indexes will be taken for calculations and for the final result (eg. only 1, 2, 3 without 1.1., 1.1.1, 1.2., 2.1. etc). I would like to avoid using filters and pivot table for this if that’s possible and all of this should be done by one macro function.
All of those files will always have only one sheet and may have different names.
I have to do that but i don’t have any knowledge about programming in VBA or even making marcos In Excel so I would be grateful for any help.
At this moment I’m working with that code and it works pretty well but i don’t know how to change name for reading Workbooks form “Source” to “*” – any name (i mean this Path to file) and loop that for bigger amout of files. Also don’t know if i should make another macro for filtering data or combine with this.
Sub CollectData() Dim i As Long, lCurrRow As Long, lRow As Long, n As Long Dim wb As Workbook, ans As VbMsgBoxResult For i = 1 To 5 Step 1 On Error Resume Next Set wb = Workbooks.Open(ThisWorkbook.Path & "Source" & i & ".xls") If Not Err.Number = 0 Then Err.Clear Set wb = Workbooks.Open(ThisWorkbook.Path & "Source " & i & ".xls") If Not Err.Number = 0 Then Err.Clear ans = MsgBox("Could not find Source " & i & " Workbook." & vbNewLine & "Do you wis" & _ "h to continue?", vbInformation vbYesNo, "Error") If ans = vbNo Then Exit Sub GoTo NextI End If End If With wb.Sheets("RAW_DATA") If Not Err.Number = 0 Then Err.Clear ans = MsgBox("Could not find Source " & i & " Workbook's 'Data Output' tab." & _ vbNewLine & "Do you wish to continue?", vbInformation vbYesNo, "Error") If ans = vbNo Then wb.Close False Exit Sub End If GoTo NextI End If If i = 1 Then lRow = 1 Else lRow = 2 End If Do Until .Range("A" & lRow).Value = vbNullString lCurrRow = lCurrRow 1 For n = 0 To 6 Step 1 Me.Range("A" & lCurrRow).Offset(ColumnOffset:=n).Value = .Range("A" & lRow).Offset(ColumnOffset:=n).Value Next n lRow = lRow 1 Loop End With NextI: wb.Close False Next i Set wb = Nothing End Sub