Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to combine amount in manys sheets into 1 summary sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to combine amount in manys sheets into 1 summary sheets
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 befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to combine amount in manys sheets into 1 summary sheets
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 befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary from multiple sheets | Excel Discussion (Misc queries) | |||
Summary of multiple sheets | Excel Worksheet Functions | |||
Combine cell totals from 5 sheets into summary in Excel | Excel Discussion (Misc queries) | |||
Summary of data from 20 sheets | Excel Discussion (Misc queries) |