Macro Works When Stepping Through, Printing First Iteration Blank (Skips Steps without Pauses)

[buckeye1414]

I am trying to auto populate two different forms to be printed via an OrderNo entered in a cell on JobCover sheet. The sheet also has a cell for JobNo which gets automatically populated from the macro below. When I manually step through the macro, it runs great; however, when I run it by clicking the macro button on the JobCover sheet, it does not work properly. I am using Broadgun PDF Maching printer for testing. It prints a blank sheet for the first iteration and everything else after the first one prints normal. Another issue is, if I switch my default printer to a physical printer, it will not print anything. I really need to get this working ASAP!!!

I also had an issue previously in which it seemed as if it was running too fast and would not populate fields properly. As if it was skipping steps. I disabled background refresh on all the queries and that seemed to help some. I then noticed a similar issue and added pauses randomly in the code to try and slow it down. I am doing something wrong.

I have the following sheets on this spreadsheet:


Job TT & Full Kit

This is sheet the macro is printing. There are multiple cells with VLOOKUPS that get populated from the JobCover sheet, JobInfo sheet. Also Job # on this sheet is referenced as =JobCover!$B$1

JobCover

This sheet has a cell in which the order number is entered which runs a query on the OrderInfo sheet as a result of the input. There is also a cell that gets populated with the Job Number (the loop in the macro populates this automatically).

JobInfo

Populated from Job Number input on JobCover sheet

OrderInfo

Populated from OrderNumber input on JobCover sheet

OpenJobData

This is a query that gets updated via the macro. It is all open job data from our ERP system.

JobPivot

PivotTable1 has data from the JobInfo sheet. There is also separate columns of non-pivot data that reference PivotTable1 and have calculated fields from the referenced info. There are multiple Vlookups on the Job TT & Full Kit sheet that reference this.

OpenJobDataPivot

PivotTable2 pulls info from the OpenJob table on the OpenJobData sheet. The PivotTable2 data is referenced on the JobPivot sheet non-pivot table columns.

Holidays

Lists company holidays for use in the WORKDAY.INTL function on the JobPivot sheet


Here is the Macro:

Sub PrintSheets()

'run time traveler and full kit sheet for all jobs on an order

'hide the updates

Application.ScreenUpdating = False

Application.EnableEvents = False



'refresh OpenJobData Query

ActiveWorkbook.Connections("OpenJobData").Refresh



'get the job numbers

ActiveWorkbook.Connections("OrderJobs").Refresh

Worksheets("OrderInfo").Activate

'refresh pivot table

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh



'count the jobs

Dim report As Worksheet

Set report = Worksheets("Job TT & Full Kit")

Dim req As Worksheet

Set req = Worksheets("JobInfo")

Dim jobno As String

Dim jobcount As Integer

jobcount = Worksheets("OrderInfo").Range("U100000").End(xlUp).Row



req.Select



If Worksheets("OrderInfo").Range("A2").Value = "" Then

MsgBox "There are no Jobs associated with this OrderNo. Check OrderNo."

GoTo Skip

End If



Worksheets("JobCover").Activate



'loop through the jobs updating the report worksheet and printing

For i = 2 To jobcount

jobno = Worksheets("OrderInfo").Range("U" & i).Value

Worksheets("JobCover").Range("B1").Value = jobno

'Call job_cover

Application.SendKeys "{ENTER}"

ActiveWorkbook.Connections("JobInfo").Refresh

Application.Wait (Now + TimeValue("00:00:01"))

Worksheets("JobPivot").Activate

Application.Wait (Now + TimeValue("00:00:01"))

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Application.Wait (Now + TimeValue("00:00:01"))



Dim lastrow As Long

lastrow = req.ListObjects("JobInfo").Range.Resize(, 1).SpecialCells(xlCellTypeVisible).Count

lastrow = req.Cells(req.Rows.Count, "A").End(xlUp).Row

Worksheets("JobInfo").Activate

req.Range("F2:F" & lastrow).Select

Selection.SpecialCells(xlCellTypeVisible).Copy

report.Range("B41").PasteSpecial xlPasteValues

Worksheets("Job TT & Full Kit").Activate

Application.Wait (Now + TimeValue("00:00:01"))

ActiveSheet.PrintOut from:=1, To:=1, Copies:=1

ActiveSheet.Range("B41:B55").ClearContents

Worksheets("JobCover").Activate

Next i



'confirm printing complete

MsgBox "All sheets have been sent to the printer."



Skip:



'show the updates

Application.EnableEvents = True

Application.ScreenUpdating = True



End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro Works When Stepping Through, Printing First Iteration Blank (Skips Steps)
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.