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

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" wrote:

> 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

Thanks Joel, I will try it

Frank

"Joel" wrote:

> 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" wrote:
>
> > 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