2 issues with Macros (Printing Pages & Not copying data when worksheet is Blank)

[RalphMHill]

Hello,

I am working on a Spreadsheet for my business (a Template for my Job Estimates) and need a little assistance. One or 2 Members have assisted me previously and I would like to say thanks to them in case they read this post. I have 2 issues my 2 different macros.

FIRST: I have a Macro CopyToMain() that runs when you click a button on the MATERIALS worksheet. The Macro copies all of the data from other worksheets on this Main Materials worksheet. However if one of the worksheets is Blank the Macro moves 3 Rows that should not move (i.e., It should skip that worksheet or Not move anything. The only way I found around it was to put a Period (.) in the First Cell (F19:AB19).

SECOND: I have incorporated another Macro PageNumberFormat() into the one above, and for troubleshooting left it in the worksheet as well. The problem is that I want this Macro to to figure out the Number of ages on the Selected Sheet and 1, fill the information into Cells (AN2:AP2) and (AU2:AW2); and second set the page setup accordingly for Printing and/or Saving to PDF file. Currently it fills the Cells with incorrect information (I.e. there are 3 Pages and it says 1 to 11).

Any and all assistance will be greatly appreciated. Spreadsheet attached.

2017 BLANK Estimate.xlsm

[rlv]

However if one of the worksheets is Blank the Macro moves 3 Rows that should not move (i.e., It should skip that worksheet or Not move anything. The only way I found around it was to put a Period (.) in the First Cell (F19:AB19).

Try putting in a test for last row (LR) before allowing the copy operation.

                    If LR >= 19 Then
                        .Range("A19:BA" & LR).Copy wsMain.Range("A" & NR)
                        NR = wsMain.Range("A" & Rows.Count).End(xlUp).Row + 1
                    End If

[RalphMHill]

Well I figured out the First part. still trying to figure out the second part.

I have incorporated another Macro PageNumberFormat() into the one above, and for troubleshooting left it in the worksheet as well. The problem is that I want this Macro to to figure out the Number of ages on the Selected Sheet and 1, fill the information into Cells (AN2:AP2) and (AU2:AW2); and second set the page setup accordingly for Printing and/or Saving to PDF file. Currently it fills the Cells with incorrect information (I.e. there are 3 Pages and it says 1 to 11).

The Number of Pages would be determined if there is data in Column "F" after Row "19".

As always any and all assistance and/or suggestions will be appreciated.