Print Timesheet PDFs

Question

So my wife manages the timesheets for her employers company and has to print a pdf timesheet for each employee that worked on client A's project 1. For instance:

Client A - Project 1 - Employee 1 - Time & Expenses
Client A - Project 1 - Employee 2 - Time & Expenses
Client A - Project 1 - Employee 3 - Time & Expenses
Client A - Project 2 - Employee 2 - Time & Expenses
Client A - Project 2 - Employee 4 - Time & Expenses
Client A - Project 3 - Employee 3 - Time & Expenses
...
Client B - Project 1 - Employee 1 - Time & Expenses
Client B - Project 2 - Employee 2 - Time & Expenses
Client B - Project 2 - Employee 3 - Time & Expenses
Client B - Project 3 - Employee 1 - Time & Expenses
Client B - Project 3 - Employee 4 - Time & Expenses
...
etc.

My wife has a pretty regular workflow that honestly doesn't need much manual manipulation so I was trying to figure out how I can automate/streamline her workflow so she can spend more time with our son.

She can have up to 10 clients, and each client might have up to 20 active projects and she might have up to 10 employees per project. She has to do this every 15 days. She compiles all the PDFs into a merged pdf for the client's project and then manually enters the info into Quickbooks for creating a summarized invoice. The invoice is then merged with the individual timesheets. All the invoices then get distributed to each client. The process repeats perpetually.

Now I am by no means experienced with coding but I believe I know more about it than the average person. For me it's more of a hobby/activity I can do when its 110 degrees outside. What I have done so far is put together a powerquery that loads all the employee timesheets and filters and cleans up the information into one master data table. My current code "works" but takes a lot of time and is probably very inefficient. The summary of my process is:

1. Copy employee name column
2. Paste employee names on a separate worksheet (temp) range A1
3. Remove Duplicates so I have a column of unique names.
4. For each name, apply "Name" filter to my master data table.
   1. Copy client column
   2. Paste clients on temp range B1
   3. Remove Duplicates so I have a column of unique clients the employee worked for.
   4. For each client the employee worked for, apply "Client" filter to my master data table.
      1. Copy project name column
      2. Paste project names on temp C1
      3. Remove duplicates so I have a column of unique project the employee worked on for the client.
      4. For each project the employee worked on, apply "Project" filter to master data table.
         1. Copy filtered data (visible cells only) from master data table
         2. Paste data into preformatted worksheet (timesheet) table.
         3. Apply filter to hide all blank rows in the timesheet table.
         4. Print timesheet to pdf in a predetermined location.
         5. Unhide rows from timesheet tables and clear contents for the next project.

I am looking for suggestions on how to improve my process to cut down on time, errors, and overall structure. I don't have any error handling in the code yet. Suggestions for that would be nice too, although ideally you have no errors right ;) . My long and ugly code is pasted below. I try to utilize variable but for some reason I get errors with them so I just rewrite the variable out and it works....

Public Sub EAS_AutoTS()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsTemp As Worksheet, po As Worksheet, que As Worksheet
Dim tempEmp As Range, tempClient As Range, tempProject As Range
Dim Emp As String, Client As String, Project As String
Dim PathName As String, SvAs As String
Dim e As Integer, c As Integer, p As Integer, ne As Integer, nc As Integer, np As Integer

Application.ScreenUpdating = False
PathName = ActiveWorkbook.Path

'Refresh Connection
wb.Connections("Query - Timesheets").Refresh
'Clear Filter on PrintOut Sheet
Set que = wb.Sheets("Data")
Set po = wb.Sheets("PrintOut")
'Create new sheet "Temp" and set Temp variable
'Sheets.Add After:=Data
'Sheets("Sheet1").Name = "Temp"
wb.Sheets("Temp").Visible = xlSheetVisible
Set wsTemp = wb.Sheets("Temp")
wsTemp.Cells.Clear
'Copy Name Column in query table to Temp Sheet A1
que.Range("A4").Select
que.AutoFilter.ShowAllData
que.Range("tblTS[Name]").Copy
wsTemp.Range("A1").PasteSpecial Paste:=xlPasteValues
'Remove Duplicates in the tempEmp Named Range to result in unique names only
wsTemp.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
'Create Dynamic Name Range for Employee Names
wb.Names.Add Name:="tempEmp", RefersTo:="=OFFSET(Temp!$A$1,0,0,COUNTA(Temp!$A:$A),1)"
'Set tempEmp = Range("tempEmp")
e = Range("tempEmp").Count

'Begin first for loop. First loop is for all employee names.
For ne = 1 To e
    'Clear all filters in query table
    que.Select
    que.AutoFilter.ShowAllData
    'Apply name to Printout sheet
    po.Range("E1") = wsTemp.Range("A" & ne).Value
    'Apply name filter to query table
    que.Range("tblTS").AutoFilter Field:=1, Criteria1:="=" & wsTemp.Range("A" & ne).Value, Operator:=xlFilterValues
    wsTemp.Range("$B:$C").ClearContents
    'Copy Client Column in query table to Temp Sheet B1
    que.Range("tblTS[Client]").SpecialCells(xlCellTypeVisible).Copy
    wsTemp.Range("B1").PasteSpecial Paste:=xlPasteValues
    'Remove Duplicate clients to result in unique clients only
    wsTemp.Range("$B:$B").RemoveDuplicates Columns:=1, Header:=xlNo
    'Create Dynamic Name Range for Client Names
    wb.Names.Add Name:="tempClient", RefersTo:="=OFFSET(Temp!$B$1,0,0,COUNTA(Temp!$B:$B),1)"
    'Set tempClient = Range("tempClient")
    'Determine number of Clients for the FOR loop.
    c = Range("tempClient").Count
    'Begin second for loop. Second loop is for all employee names.
    For nc = 1 To c
        'Apply client filter to query table
        que.Range("tblTS").AutoFilter Field:=6
        que.Range("tblTS").AutoFilter Field:=5, Criteria1:="=" & wsTemp.Range("B" & nc).Value, Operator:=xlFilterValues
        'Copy Project Column in query table to Temp Sheet C1
        wsTemp.Range("$C:$C").ClearContents
        que.Range("tblTS[Project Name]").SpecialCells(xlCellTypeVisible).Copy
        wsTemp.Range("C1").PasteSpecial Paste:=xlPasteValues
        'Remove Duplicates in the tempProject Named Range to result in unique projects only
        wsTemp.Range("$C:$C").RemoveDuplicates Columns:=1, Header:=xlNo
        'Create Dynamic Name Range for Project Names
        wb.Names.Add Name:="tempProject", RefersTo:="=OFFSET(Temp!$C$1,0,0,COUNTA(Temp!$C:$C),1)"
        'Set tempProject = Range("tempProject")
        'Determine number of Clients for the FOR loop.
        p = Range("tempProject").Count
        'Begin third (final) for loop: Third loop is for project filtering
        For np = 1 To p
            'Apply Project filter to query table
            If p <> 1 Then que.Range("tblTS").AutoFilter Field:=6, Criteria1:="=" & wsTemp.Range("C" & np).Value, Operator:=xlFilterValues
            'Copy query table to Printout sheet
            If po.Range("A3").Value <> "" Then po.AutoFilter.ShowAllData
            po.Range("tblPrint").ClearContents
            Range("tblTS[Date]:tblTS[Total Expenses]").SpecialCells(xlCellTypeVisible).Copy
            po.Select
            po.Range("A3").Select
            po.Range("A3").PasteSpecial Paste:=xlPasteValues
            'Filter (hide) blank cells.
            po.Range("tblPrint").AutoFilter Field:=1, Criteria1:="<>"
            'Print PDF to folder
            po.Range("W1").NumberFormat = "@"
            po.Range("W1").Value = Format(po.Range("W1").Value, "m-d-yy")
            SvAs = PathName & "\PDFs\" & po.Range("D3") & "_" & po.Range("E3") & "_" & po.Range("E1") & "_" & po.Range("W1") & ".pdf"
            po.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SvAs, Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next 'End of third for loop
    Next 'End of second for loop
Next 'End of first for loop
po.Range("A3").Select
po.AutoFilter.ShowAllData
po.Range("tblPrint").ClearContents
po.Range("E1").Value = ""
po.Range("W1").Value = ""
wsTemp.Range("$A:$C").ClearContents
wsTemp.Visible = xlSheetHidden
e = 0
c = 0
p = 0
ne = 0
nc = 0
np = 0
que.Select
que.AutoFilter.ShowAllData
Application.ScreenUpdating = True
MsgBox "Timesheets have been created at the following location:" & vbNewLine & PathName & "\PDFs", _
    vbOKOnly, "Finished!"
End Sub

Answer 1

Eh, you would really benefit here from PowerQuery.

All the Range stuff and select and clears, I mean you need to learn how to fundamentally code with variables to improve that.

But I would say, if you're taking a table and sorting, PQ has an awesome visual editor and clickable steps that anyone, including your wife can learn.

It's a slippery slope to use VBA with your significant other, eventually, the errors and problems are going to weigh on the relationship. PQ is gonna be your Dr. Phil here and get everyone living their best life on their own.

See WillWulliems reply here about how to automate it: here