How to combine amount in manys sheets into 1 summary sheets

[Frank Situmorang]

Hello,

Thi is my worksheets which are identical in column header but not in the
number of rows an names. I want to combine the calculation of the employee
income tax.

This is my worsheet layout approximately

1. Name. Tx ID Gross salary Tax net Salary
--------- ------- -------------- ------- -----------
Mr A 253 25,000 5000 20,000
Mr. B
and so forth

in sheet Jan could only be 50 labors and and in sheet Feb could be 40 labors
while
in Mar could rise up to 60.

I appreciate if someone can help me how can I combine the 12 sheets in front
for summary sheets ( Total).

Thanks,

Frank

 

[Joel]

Try this code. It creates a summary sheet called "Summary" and assumes the
sheet names are abbreviated months Jan, Feb, Mar. The code uses the function
monthnames to get the abbrieviate months. You can change the TRUE to False
to get the non-abbreviated names. the code also sorts the summary sheet by
the Tax ID column B.

Sub combinetaxes()

'check if summary sheet exists
found = False
For Each Sht In Sheets
If Sht.Name = "Summary" Then
found = True
End If
Next Sht

If found = False Then
Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Summary"
End If

Set SumSht = Sheets("Summary")

With SumSht
For MyMonth = 1 To 12
MName = MonthName(MyMonth, abbreviate:=True)
Set Sht = Sheets(MName)
If MyMonth = 1 Then
'copy header row only once
Sht.Rows(1).Copy Destination:=SumSht.Rows(1)
End If

SourceLastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
SumLastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumNewRow = SumLastRow + 1
'copy rows skipping header rows
Sht.Rows("2:" & SourceLastRow).Copy _
Destination:=SumSht.Rows(SumNewRow)

'sort Sumary sheet by Tax ID column B
SumLastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Rows("1:" & SumLastRow).Sort _
header:=xlYes, _
key1:=.Range("B1"), _
order1:=xlAscending
Next MyMonth
End With

End Sub

 

[Frank Situmorang]

Thanks Joel, I will try it

Frank

Try this code. It creates a summary sheet called "Summary" and assumes the
sheet names are abbreviated months Jan, Feb, Mar. The code uses the function
monthnames to get the abbrieviate months. You can change the TRUE to False
to get the non-abbreviated names. the code also sorts the summary sheet by
the Tax ID column B.

Sub combinetaxes()

'check if summary sheet exists
found = False
For Each Sht In Sheets
If Sht.Name = "Summary" Then
found = True
End If
Next Sht

If found = False Then
Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Summary"
End If

Set SumSht = Sheets("Summary")

With SumSht
For MyMonth = 1 To 12
MName = MonthName(MyMonth, abbreviate:=True)
Set Sht = Sheets(MName)
If MyMonth = 1 Then
'copy header row only once
Sht.Rows(1).Copy Destination:=SumSht.Rows(1)
End If

SourceLastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
SumLastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumNewRow = SumLastRow + 1
'copy rows skipping header rows
Sht.Rows("2:" & SourceLastRow).Copy _
Destination:=SumSht.Rows(SumNewRow)

'sort Sumary sheet by Tax ID column B
SumLastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Rows("1:" & SumLastRow).Sort _
header:=xlYes, _
key1:=.Range("B1"), _
order1:=xlAscending
Next MyMonth
End With

End Sub