Solved: Generating Payslip

[gnod]

hi,

is it possible to generate a payslip using macro that will be based on how many plate no. in the table (Data sheet).. but i only did is a formula using a combination of SUM and IF function (Payslip sheet).. The output should be look like in the "Output sheet"..

:help

Thanks..

[Bob Phillips]

Option Explicit

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim col As Long
Dim baseRow As Long
Dim aryheads


aryheads = Array("Service Fee", "Fuel", "Toll / Parking Fee", "", _
"DEDUCTIONS:", "Maintenance Fund", "Cash Bond", _
"Fuel Payment", "Short Remittance,", "", "Net Payment")


With Worksheets("Data")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
col = 4
For i = 3 To iLastRow

col = col - (col = 1) * 3 + (col = 4) * 3
baseRow = ((i - 3) \ 2) * 16 + 1
Worksheets("Output").Cells(baseRow, col).Value = .Range("A1").Value
Worksheets("Output").Cells(baseRow, col).Font.Bold = True
Worksheets("Output").Cells(baseRow + 1, col).Value = .Cells(i, "A").Value
Worksheets("Output").Cells(baseRow + 3, col).Resize(11) = Application.Transpose(aryheads)
Worksheets("Output").Cells(baseRow + 1, col).Interior.ColorIndex = 36
Worksheets("Output").Cells(baseRow + 7, col).Font.Bold = True
Worksheets("Output").Cells(baseRow + 13, col).Font.Italic = True
Worksheets("Output").Cells(baseRow + 1, col + 1).FormulaR1C1 = _
"=VLOOKUP(R2C" & col & ",Data!C1:C2,2,FALSE)"
Worksheets("Output").Cells(baseRow + 1, col + 1).HorizontalAlignment = xlCenter
Worksheets("Output").Cells(baseRow + 3, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_ServiceFee)"
Worksheets("Output").Cells(baseRow + 4, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_Fuel)"
Worksheets("Output").Cells(baseRow + 5, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_TollParkingFee)"
Worksheets("Output").Cells(baseRow + 8, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_MaintenanceFund)"
Worksheets("Output").Cells(baseRow + 9, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_CashBond)"
Worksheets("Output").Cells(baseRow + 10, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_FuelPayment)"
Worksheets("Output").Cells(baseRow + 11, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_Shortunremitted)"
Worksheets("Output").Cells(baseRow + 13, col + 1).FormulaR1C1 = _
"=SUM(R4C:R6C)-SUM(R9C:R12C)"
Worksheets("Output").Cells(baseRow + 13, col + 1).Font.Bold = True
Next i
End With

With Worksheets("Output")
.Columns("A").ColumnWidth = 16
.Columns("B").ColumnWidth = 20
.Columns("C").ColumnWidth = 3
.Columns("D").ColumnWidth = 16
.Columns("E").ColumnWidth = 20
.Columns("B").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
.Columns("E").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
End With

End Sub

[gnod]

Thanks XLD :thumb :bow:

i'll study your code because i still have other issues to solve:
- print all the payslip based on the no. of plate no. in the Data table or the user can select the plate no. to print the payslip..

because it should be printable so that the drivers can have a copy of their payslip.. i'll try to solve but if i can't make it pls help me again..


thanks..

[gnod]

col = col - (col = 1) * 3 + (col = 4) * 3
baseRow = ((i - 3) \ 2) * 16 + 1


how do you come up with this kind of formula? :think: can you pls explain it to me..
thanks..

[Bob Phillips]

As you are printing two up, we need to calculate the column number for 1sta nd 2nd, for 3rd and 4th, etc. The column is 1 or 4 so I just toggle it in line 1.

You also need to calculate a start row for the payslip, again based on 1st and 2nd, 3rd and 4th, etc. I integer divide by two so that the 1st and second give the same row, and multiply by 16 and the size of each payslip is 16 rows.

[gnod]

thanks.. :friends:

[gnod]

With Worksheets("Output")
.PageSetup.TopMargin = Application.InchesToPoints(0.5)
.PageSetup.BottomMargin = Application.InchesToPoints(0.25)
.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
.PageSetup.RightMargin = Application.InchesToPoints(0.25)
.PageSetup.CenterHorizontally = True
.Columns("A").ColumnWidth = 16
.Columns("B").ColumnWidth = 20
.Columns("C").ColumnWidth = 3
.Columns("D").ColumnWidth = 16
.Columns("E").ColumnWidth = 20
.Columns("B").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
.Columns("E").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
End With

i use the code and set the pagesetup but it is not nice to look at if the payslip to be generate is more than 10. with this pagesetup it will only fit the 6 payslip per page.. how do i set the next 6 set of payslip in the other page?

:help

thanks..

[Bob Phillips]

I don't understand what you are saying?

[gnod]

for ex: we have 20 Plate # in the Data sheet to generate payslip. I set the top, bottom, left & right margin. if i run the ProcessData procedure and look at the print preview, some of the data is cut-off. pls refer to this file and compare the "Output sheet" and "Output2 sheet" when you click "print preview".

thanks

[Aussiebear]

I think gnod is looking for a page break function

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim col As Long
Dim baseRow As Long
Dim aryheads


aryheads = Array("Service Fee", "Fuel", "Toll / Parking Fee", "", _
"DEDUCTIONS:", "Maintenance Fund", "Cash Bond", _
"Fuel Payment", "Short Remittance,", "", "Net Payment")


With Worksheets("Data")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
col = 4
iBreak = 3
For i = 3 To iLastRow

col = col - (col = 1) * 3 + (col = 4) * 3
baseRow = ((i - 3) \ 2) * 16 + 1

If baseRow > 1 And baseRow Mod 48 = 1 And col = 1 Then
Worksheets("Output").HPageBreaks.Add Before:=.Cells(baseRow, "A")
End If

Worksheets("Output").Cells(baseRow, col).Value = .Range("A1").Value
Worksheets("Output").Cells(baseRow, col).Font.Bold = True
Worksheets("Output").Cells(baseRow + 1, col).Value = .Cells(i, "A").Value
Worksheets("Output").Cells(baseRow + 3, col).Resize(11) = Application.Transpose(aryheads)
Worksheets("Output").Cells(baseRow + 1, col).Interior.ColorIndex = 36
Worksheets("Output").Cells(baseRow + 7, col).Font.Bold = True
Worksheets("Output").Cells(baseRow + 13, col).Font.Italic = True
Worksheets("Output").Cells(baseRow + 1, col + 1).FormulaR1C1 = _
"=VLOOKUP(R" & baseRow + 1 & "C" & col & ",Data!C1:C2,2,FALSE)"
Worksheets("Output").Cells(baseRow + 1, col + 1).HorizontalAlignment = xlCenter
Worksheets("Output").Cells(baseRow + 3, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_ServiceFee)"
Worksheets("Output").Cells(baseRow + 4, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_Fuel)"
Worksheets("Output").Cells(baseRow + 5, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_TollParkingFee)"
Worksheets("Output").Cells(baseRow + 8, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_MaintenanceFund)"
Worksheets("Output").Cells(baseRow + 9, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_CashBond)"
Worksheets("Output").Cells(baseRow + 10, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_FuelPayment)"
Worksheets("Output").Cells(baseRow + 11, col + 1).FormulaR1C1 = _
"=SUMIF(Data_PlateNum,R2C" & col & ",Data_Shortunremitted)"
Worksheets("Output").Cells(baseRow + 13, col + 1).FormulaR1C1 = _
"=SUM(R4C:R6C)-SUM(R9C:R12C)"
Worksheets("Output").Cells(baseRow + 13, col + 1).Font.Bold = True
Next i
End With

With Worksheets("Output")
.PageSetup.TopMargin = Application.InchesToPoints(0.5)
.PageSetup.BottomMargin = Application.InchesToPoints(0.25)
.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
.PageSetup.RightMargin = Application.InchesToPoints(0.25)
.PageSetup.CenterHorizontally = True
.Columns("A").ColumnWidth = 16
.Columns("B").ColumnWidth = 20
.Columns("C").ColumnWidth = 3
.Columns("D").ColumnWidth = 16
.Columns("E").ColumnWidth = 20
.Columns("B").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
.Columns("E").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
End With

End Sub

[gnod]

:clap: thanks xld..

i add another feature using data validation on my payslip template when i'm doing it at my workplace.. i'll post it on another thread because i have another question on how to select more than 1 plate# using data validation because i can only select one plate# then generate a payslip..

once again, thanks :bow: