EDIT CODE

G

gleamng

Board Regular
Joined
Oct 8, 2016
Messages
98
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
  8. 2007
  9. 2003 or older
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Good evening house
please i need help with the macro below,
the macro generate payslip for each row (that is if i have 10 records, it created 10 pdf files
what i want now is for the macro to generate one single file with each record per page (i.e 10pages pdf file)

thanking you for your help


VBA Code: 
Option Explicit

Sub Slip()
On Error Resume Next
MkDir "C:\Payslip"
Sheet5.Range("A1:D32").ExportAsFixedFormat xlTypePDF, Filename:="C:\Payslip\" & Sheet5.Range("F3").Value, openafterpublish:=False
End Sub

Sub PDF_PAYSLIP()
Application.ScreenUpdating = False
On Error Resume Next
MkDir "C:\Payslip"
Dim x As Long
Dim y As Long
x = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For y = 4 To x
If Len(Sheet2.Cells(y, 1).Value) > 0 Then
Sheet5.Range("F3").Value = Sheet2.Cells(y, 1)
End If
Call Slip
Next y

MsgBox "Pay Slips successfully generated"
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
what i want now is for the macro to generate one single file with each record per page (i.e 10pages pdf file)
Click to expand...

Isn't that what the current macro is doing ?
 
Upvote 0
You could have your Slip macro copy the relevant range of Sheet5 to an open Excel workbook of your choice (let's call it myWB) before you export Sheet5 to the single pdf document. Then when all single pdf's have completed, select all relevant sheets in myWB and export to pdf.
 
Upvote 0
I don't think it's going to work because the record originally is over 20,000
 
Upvote 0
Can't you list them all on Sheet5 and add page breaks then send them to PDF?

VBA Code: 
Sub PDF_PAYSLIP()
    Application.ScreenUpdating = False
    On Error Resume Next
    MkDir "C:\Payslip"
    Dim x As Long
    Dim y As Long
    x = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    For y = 4 To x
        If Len(Sheet2.Cells(y, 1).Value) > 0 Then
            Sheet5.Cells(y - 1, "F").Value = Sheet2.Cells(y, 1)
            Sheet5.HPageBreaks.Add Sheet5.Cells(y, 1)
        End If
    Next y
    Call Slip
    MsgBox "Pay Slips successfully generated"
End Sub
Please note that Excel limits the number of page breaks to 1026 and so you will have to do a loop if you have 20000 records. Not sure if there is a limit to the number of PDF pages that you can have.
 
Last edited:
Upvote 0
Thanks for your help, the macro only splitted a page into number of records in sheet2 (it only generate the first records playslip and split it into 10pages)
 
Upvote 0
Do you not have a page where there is a separate line for each payslip? and what exactly are you supposed to be copying as you are only copying one cell in your original code?
gleamng said:
the macro generate payslip for each row
Click to expand...
 
Upvote 0
MARK858 said:
Do you not have a page where there is a separate line for each payslip?
Click to expand...

Yes i have, i wish i can upload the file so you can see it.
I have two sheets, sheet2 contains payslip data and sheet5 has payslip template which i use vlookup to generate needed details from sheet2.
But the macro i posted generate a PDF file per individual record so i want the macro to generate a single bulk PDF file for all the records.
 
Upvote 0
gleamng said:
Yes i have, i wish i can upload the file so you can see it
Click to expand...

Use the boards XL2BB addin to post a sample of your data from both sheets (in particular Sheet5), the XL2BB icon in the reply window is a link to the download and instructions.

Make sure that you amend any sensitive data before posting.
 
Upvote 0
EMPLOYEE DATA

NewPaySlip.xlsm
ABCDEFGHIJKLMNOP
1Verification NumberPSNLGAMobileSurNameFirstNameMiddleNameRetirement DateSexGradeStepTotal DeductionGrossNetPayYearMonth
2423573423556330PAIG GE+070689868BROSMARTHBABA1/1/2031M0711910.833226.2731315.472020September
3649916264973223INEAST GE+070691281BALDDOLARTMOROMOKE6/12/2038F1013395.1648573.3945178.232020September
4770319077018283MORE GE+070550687SANSIRAHFUNKE12/24/2033F1325362.8765346.1259983.252020September
5915334091588143DUE GE+070343562USEYORH9/1/2041M1013395.1648573.3945178.232020September
6743451674368188REST GE+070329842MOHHRAYMONDABDULKADRI12/29/2022M1519790.1899694.4489904.262020September
7594162759402238INSOT GE+080623216MOONBROSTUNDE3/17/2040M1013395.1648573.3945178.232020September
8684265768421267INSOT GE+080304193LOLLYBALDOLUBUNMI7/15/2038F1013395.1648573.3945178.232020September
9435968243558361REST GE+070381473SABIESANNAHALLAH5/22/2047M0812221.2635420.6333199.372020September
10409569940934352MORE GE+080385825JACOBUSEJOSEPH9/29/2045M0812357.6237091.0334733.412020September
11078172407830308OFF GE+080715485ZEBMONDFUNMILAYO9/1/2024F1519759.6799694.4489934.772020September
12045580904562108REST GE+070327098YORHSABIEMOJIRAYO10/30/2039F0912861.0942470.5139609.422020September
13597781959774101REST GE+080648060RAYMONDJACOBBOLA3/17/2043F0912848.1442470.5139622.372020September
14308284430885189REST GE+080336592ALIBIZEBWUMI3/9/2039F0912835.1842470.5139635.332020September
15843188484392141DUE GE+081391551SEUSMOHAMMED10/7/2029M0711859.3531954.2730094.922020September
16689395768969103GASE GE+080591701SAGEJACOBOLUTOYIN12/12/2030F0912848.1442470.5139622.372020September
17219397421913372INSOT GE+081384852SRONEZEB8/11/2041M0812213.3135420.6333207.322020September
18001210660008217INEAST GE+080352898BIDEMYORHYEMI1/1/2037F12146923.1257535.3310612.212020September
19374111303708171DUN GE+080675062SOLERAYMONDHELEN2/11/2036F1013305.8748573.3945267.522020September
20702212517080355DUN GE+070392399SESAMEALIBITEMITOPE6/14/2047F0812213.3135420.6333207.322020September
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A20Cell ValueduplicatestextNO


PAYSLIP TEMPLATE
NewPaySlip.xlsm
ABCDEF
1
2
3Ver. No.4235734235
4
5
6
7
8
9EMPLOYEE PAY SLIP FOR THE MONTH OF SEPTEMBER, 2020
10
11
12Personal Details:
13Employee Name:BROS, MARTH BABA
14Verification Number:4235734235Grade:07
15PSN Number:56330Step:01
16Local Government:PAIG GEGender:MALE
17Retirement Date:January 1, 2031Mobile Number:+070689868
18
19
20Payment Details:
21Deductions:₦1,910.80
22Total Deduction:₦1,910.80
23Gross Pay:₦33,226.27
24Net Pay:₦31,315.47
Sheet2
Cell Formulas
RangeFormula
A9A9=IFERROR(UPPER("EMPLOYEE PAY SLIP FOR THE MONTH OF "&VLOOKUP(F3,Sheet1!$A$2:$P$20,16,0)&", "&VLOOKUP(F3,Sheet1!$A$2:$P$20,15,0)),"")
B13B13=IFERROR(+VLOOKUP(B14,Sheet1!$A$2:$P$20,5,0)&", "&+VLOOKUP(B14,Sheet1!$A$2:$P$20,6,0)&" "&+VLOOKUP(B14,Sheet1!$A$2:$P$20,7,0),"")
B14B14=F3
B15B15=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,2,0),"")
B16B16=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,3,0),"")
B17B17=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,8,0),"")
D14D14=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,10,0),"")
D15D15=IFERROR("0"&+VLOOKUP(F3,Sheet1!$A$2:$P$20,11,0),"")
D16D16=IFERROR(IF(+VLOOKUP(F3,Sheet1!$A$2:$P$20,9,0)="M","MALE","FEMALE"),"")
D17D17=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,4,0),"")
B21B21=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,12,0),"")
B22B22=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,12,0),"")
B23B23=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,13,0),"")
B24B24=IFERROR(+VLOOKUP(F3,Sheet1!$A$2:$P$20,14,0),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA to create bulk PDF for selected ID
Replies
3
Views
83
Joe4
Joe4
T
  • Solved
VBA to email pay slip
2
Replies
12
Views
2K
tayomi
T
S
  • Question
saving as pdf with changing additional sheet name
Replies
1
Views
75
John_w
J
T
  • Question
This is so slow I have to run it overnight
Replies
4
Views
251
kevin9999
K
I
  • Solved
Add date to pdf output
Replies
5
Views
107
Joe4
Joe4

Forum statistics

Threads
1,215,379
Messages
6,124,609
Members
449,174
Latest member
ExcelfromGermany

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top