As LCPtracker has 90+ robust validations that run on each payroll line entered, it is necessary to only include 200 lines at a time when uploading your file as a Contractor. The following guide will provide the Contractor with a tool to assist with creating multiple files of the payroll upload prior to entering the data into LCPtracker.
Payroll Data Added to LCPtracker Upload File
Once payroll has been processed, the contractor will need a process to map the data from their accounting system, such as SAP, to flow into the format of our CPR upload file.
This file format can be found within the LCPtracker Contractor login page by clicking on 1. Payroll Records > Upload Records > Download spreadsheet template.
Record a Macro to Split the File at 200 Lines
Once you have your payroll data in the Excel (.xls) file, you will need to first show the Developer tab on your Excel Workbook file which is hidden by default. To enable this, you will need to first complete the following steps.
Click on File > select Options in the lower left corner
Within the Options library, select Customize Ribbon and then select Main Tabs from the “Choose commands from:” dropdown
Select Developer, click Add >>, and then click OK to add it to your Customized Ribbon
After adding the Developer tab, you are ready to create the Macro to split the file.
Recording the Macro
- Select the Developer tab
- Click Visual Basic
- Select Insert and click Module
A blank page will open for you to copy the following code: (copy full script until End Sub)
Sub SplitExcelFile()
Dim ws As Worksheet
Dim newWs As Worksheet
Dim newWb As Workbook
Dim lastRow As Long
Dim maxRows As Long
Dim rowCount As Long
Dim fileCount As Long
Dim header As Range
Dim savePath As String
Dim startRow As Long
Dim endRow As Long
Dim col As Range
maxRows = 199
fileCount = 1
Set ws = ThisWorkbook.ActiveSheet
lastRow = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set header = ws.Rows(1)
startRow = 2
Do While startRow <= lastRow
endRow = startRow + maxRows - 1
If endRow > lastRow Then endRow = lastRow
Set newWb = Workbooks.Add
Set newWs = newWb.Sheets(1)
header.Copy Destination:=newWs.Rows(1)
ws.Rows(startRow & ":" & endRow).Copy
newWs.Rows(2).PasteSpecial Paste:=xlPasteValues
' Preserve date format
For Each col In ws.Rows(1).Columns
If IsDate(ws.Cells(2, col.Column).Value) Then
newWs.Columns(col.Column).NumberFormat = "m/d/yyyy"
End If
Next col
savePath = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save Workbook " & fileCount)
If savePath <> "False" Then
newWb.SaveAs Filename:=savePath
End If
fileCount = fileCount + 1
startRow = endRow + 1
Loop
MsgBox "Splitting complete!"
End Sub
The below view will display once the script is pasted in the box:
Close the Module and Visual Basic and proceed to running the Macro.
Running the Macro to Split the Files into 200 Lines
- Click Macros
- The code you created will show and you will click Run
- This will start creating your files of 200 lines and will continue until the splitting is complete. Each file will need to be saved to a File Folder with whatever naming convention you want to use and in a location where you will go to select the file to upload into LCPtracker.
- You will receive the following message once completed.
Ready to Upload Payroll Files to LCPtracker
Upload Records
A Contractor can either enter their payrolls manually or can choose to upload the payroll data using the LCPtracker Upload Record functionality. The Upload Records function is intended to provide a function for the uploading of payroll records from a spreadsheet.
A very common upload method is to use the standard interface template defined by LCPtracker. This is an Excel spreadsheet template with specifically defined columns.
To download the excel template, go to: Payroll Records > Upload Records > Click the “Download spreadsheet template” hyperlink. This allows you to download the free spreadsheet template required to upload payroll data.
Additionally, the payroll provider you use may be able to format a customized report that produces a file for export that can be uploaded into LCPtracker. To see the current list of companies, go to www.lcptracker.com > Resources > Preferred Providers.
This spreadsheet will provide you with instructions on how to use this function and a legend explaining each column.
Please note, when uploading payroll records using the upload template, the file will be limited to 200 payroll records per upload. Please reference the Macro instructions above for a time saver.
Should you need further assistance after reviewing the spreadsheet and instructions, please contact LCPtracker Support at 714-669-0052 Option 4.
Uploading Process
Regardless of the interface or use of the Excel Spreadsheet template, the uploading process is the same and very simple.
Remember the upload process will also create your employees in Set Up, Add/Edit Employees section if you did not already manually enter. You may need to manually adjust certain requirements on your employee setup depending on the Administrator requirements.
Go to 1. Payroll Records > Upload Records
- Select the week end date (or leave blank if you are uploading several weeks at a time in one upload and your upload includes the week end dates).
- Choose the Project that you are going to upload for.
- Choose File from your computer to upload; and
- Click Upload - as your file uploads, you will be able to see the data check validations working
Note: your file must not exceed 200 payroll records.
Once the file finishes creating the records and running the validations for the individual rows, please select the next file and click Upload. Repeat until all records have been entered for the week you are certifying your payroll for.
If there are issues the system does not accept on a row, it will give you pop-up message.
Once you have uploaded your files and matched the crafts from your payroll system to a craft/classification for the Prevailing Wages assigned to your project, you are now ready to move to steps 2 and 3.
The tab 2. Notices is where you will clear any potential triggered notices and tab 3. Certification is where you will certify your payroll record by entering your eSignature password to generate the PDF for your certified payroll record (CPR).
For more details on this process, you can click on the “Knowledge Base” button and access the Contractor User Guide.
Contacting the LCPtracker Support Team
Should you need additional assistance, please contact our LCPtracker Support team Monday – Friday, 5am to 5:30pm PST through one of the following:
- Call 714-669-0052 option 4 or
- Select Live Chat (during normal business hours) or
- Email support@lcptracker.com
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article