(Solved) – Consolidate data from multiple workbooks in a single worksheet

(solved)-–-consolidate-data-from-multiple-workbooks-in-a-single-worksheet

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.

I’m adding some screenshots how i see that should working (for Phase 1 and Term 40) and I hope that will help to understand my problem.enter image description here

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

Leave a Reply

Your email address will not be published. Required fields are marked *