VBA to pull data from Multiple Worksheets into One

[Haree]

Hello,
I am very new to Excel VBA, i have worked on a few codes based on the forums available on MR Excel. I currently require a vba code for Debtors Management. I have 12 worksheets in a workbook each one for a separate month, in which i have a column called category in which i enter Debtors to identify them and later when i receive them i categorize them under the name Debtors Received.
Now i want a separate sheet called debtors to pull all the rows categorized as debtors and debtors received from all the 12 sheets and show me the current Balance.Since i am very new i am quite blank. Any ideas would be appreciated.
Thank You
Regards
Haree

 

[mumps]

You are very welcome.

 

[Haree]

Hello Mumps,
Hope you are doing good and safe right now,
I just need a small favor in the code which you helped me with

Range("D" & LastRow + 1).Formula = "=sum(D3:D" & LastRow & ")"
.Range("H" & LastRow + 1).Formula = "=sum(H3:H" & LastRow & ")"
.Range("L4:L" & LastRow).Formula = "=SUMIF(B2:D" & LastRow & ",K4,D2:D" & LastRow & ")-SUMIF(F2:H" & LastRow & ",K4,H2:H" & LastRow & ")"
.Range("L" & LastRow + 1).Formula = "=sum(L4:L" & LastRow & ")"

These formula's starting Numbers should be fixed, for example instead of H3 i want $H$3. Kindly help me out

 

[mumps]

Simply insert the $ where you want it in the formulas: "=sum($H3:$H" & LastRow & ")"
Is there a reason why you want this change? It won't make any difference in the result.

 

[Haree]

Thank You sir, Its just that it moves the formula It goes from H3 then From H4 then from H5 thats y i had asked for that absolute reference

 

[Haree]

Hello @mumps
This is the code which you had provided me. Must say its been of great help to me. I have tweaked a bit in your version. I have a small issue in it. We have two columns named Debtors and Debtors Received and adjacent to them we have their respective names. I had asked this code basically to know my debtors balances, So it gets the name and matches their debtors and debtors received. At present i have a situation certain times i would have to account Debtors Received before Debtors. What happens in this case is it doesnt show their names in the Debtors Sheet. (But if i put their name in debtors first and then using it for Debtors received i am able to get it ) Any Suggestions ?

Sub GetBalance()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet, CustName As Range, desWS As Worksheet, d As Long, h As Long, fnd As Range
    Set desWS = Sheets("Debtors")
    With desWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If LastRow < 3 Then LastRow = 3
        .Range("$A$3:H" & LastRow).ClearContents
        .Range("$K$3:L" & LastRow).ClearContents
    End With
    For Each ws In Sheets
        If ws.Name <> "Debtors" Or ws.Name <> "Total" Or ws.Name <> "Names" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each CustName In ws.Range("$J$2:J" & LastRow)
                If CustName <> "" Then
                    If WorksheetFunction.CountIf(desWS.Range("K:K"), CustName) = 0 Then
                        With desWS
                            .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0) = CustName
                        End With
                    End If
                End If
            Next
        End If
    Next ws
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each CustName In desWS.Range("$K$3:K" & LastRow)
        For Each ws In Sheets
            If ws.Name <> "Debtors" Or ws.Name <> "Names" Or ws.Name <> "Total" Then
                With ws
                    If WorksheetFunction.CountIf(.Range("D:D"), CustName) > 0 Then
                        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        .Range("$A$1:J" & LastRow).AutoFilter Field:=4, Criteria1:=CustName
                        Set fnd = .Range("C:C").SpecialCells(xlCellTypeVisible).Find("Debtors Received")
                        If Not fnd Is Nothing Then
                            .Range("$A$1:J" & LastRow).AutoFilter Field:=3, Criteria1:="Debtors Received"
                            With desWS
                                ws.Range("$A$2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0)
                                ws.Range("$D$2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0)
                                ws.Range("$E$2:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0)
                                ws.Range("$G$2:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "H").End(xlUp).Offset(1, 0)
                            End With
                            .Range("A1").AutoFilter
                        End If
                    End If
                    If WorksheetFunction.CountIf(.Range("J:J"), CustName) > 0 Then
                        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        .Range("$A$1:J" & LastRow).AutoFilter Field:=10, Criteria1:=CustName
                        Set fnd = .Range("I:I").SpecialCells(xlCellTypeVisible).Find("Debtors")
                            If Not fnd Is Nothing Then
                                .Range("$A$1:J" & LastRow).AutoFilter Field:=9, Criteria1:="Debtors"
                                With desWS
                                    ws.Range("$H$2:H" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
                                    ws.Range("$J$2:J" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
                                    ws.Range("$K$2:K" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0)
                                    ws.Range("$L$2:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0)
                                End With
                                .Range("A1").AutoFilter
                            End If
                        .Range("A1").AutoFilter
                    End If
                    If ws.AutoFilterMode Then ws.AutoFilterMode = False
                End With
            End If
        Next ws
    Next CustName
    With desWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("D" & LastRow + 1).Formula = "=sum($D$3:D" & LastRow & ")"
        .Range("H" & LastRow + 1).Formula = "=sum($H$3:H" & LastRow & ")"
        .Range("$L$3:L" & LastRow).Formula = "=SUMIF($B$2:D" & LastRow & ",K3,$D$2:D" & LastRow & ")-SUMIF($F$2:H" & LastRow & ",K3,$H$2:H" & LastRow & ")"
        .Range("L" & LastRow + 1).Formula = "=sum($L$3:L" & LastRow & ")"
    End With
    Application.ScreenUpdating = True
End Sub

P.S Can i use Case in the worksheets section instead of OR

Thanks in advance

 

[mumps]

Please upload an updated file and explain in detail what you want to do referring to specific cells, rows, columns and sheets