Print Hidden Dynamic Worksheet

beeker · Jul 1st 2005

Good day to all,
I would like to know if I can print a hidden worksheet called Price List (sheet 2) that has a dynamic range? I have three spreadsheets called Costing, Pricing and Do Not Delete. Pricing and Do Not Delete are hidden and protected worksheets. I would like to be able to create a Macro that will print only the "Pricing" worksheet and keep it hidden. This sheet is also dynamic in range based upon input from "Costing". Last record in $A:$A over to $L:$L

Can this be done. I see where I can print hidden worksheets and dynamic, but can they be combined and the view to change to the user?

Thanks Beeker

Barrie Davidson · Jul 1st 2005

Re: Print Hidden Dynamic Worksheet

Would this work?

Code

Application.ScreenUpdating = False
Sheets("Pricing").Visible = True
Sheets("Pricing").PageSetup.PrintArea = "$A$1:$L$" & _
    Sheets("Costing").Range("A65536").End(xlUp).Row
Sheets("Pricing").PrintOut
Sheets("Pricing").Visible = False
Application.ScreenUpdating = True

**royUK** · Jul 1st 2005

Re: Print Hidden Dynamic Worksheet

Take a look at CurrentRegion & UsedRange - these could be used to set the PrintArea.
Alternatively,

Code

Dim MyPrint As Range


    Set MyPrint = Sheets("Pricing").Range("A1", Range("L65536").End(xlUp))
    Application.ScreenUpdating = False    'hide actions
      ' With Sheet2   'This would allow for sheet name being change
        With Sheets("Pricing")
        .Visible = xlSheetVisible
        .PageSetup.PrintArea = MyPrint
        .PrintPreview    'to check
        .PrintOut    'to print
        .Visible = xlSheetVeryHidden
    End With
    Application.ScreenUpdating = True

Display More

beeker · Jul 11th 2005

Re: Print Hidden Dynamic Worksheet

Thanks Barrie,
I am using the method you suggested. However, it gives me one additional blank page at the very end. I have checked everywhere for some type of contents in the referenced cells but nothing but a formula present. Some worksheets are 2 pages of information and it prints 3. Is there something I have missed here. Unfortunately, I embedded this on 54 worksheets b4 I did a complete workbook printing, yuck

Thanks
Beeker

Barrie Davidson · Jul 14th 2005

Re: Print Hidden Dynamic Worksheet

Quote from beeker

Thanks Barrie,
I am using the method you suggested. However, it gives me one additional blank page at the very end. I have checked everywhere for some type of contents in the referenced cells but nothing but a formula present. Some worksheets are 2 pages of information and it prints 3. Is there something I have missed here. Unfortunately, I embedded this on 54 worksheets b4 I did a complete workbook printing, yuck

Thanks
Beeker

Do you mean that there's formula in column A that's returning a blank (and is being printed)? If yes, what column could be used to determine the last row?

beeker · Jul 15th 2005

Re: Print Hidden Dynamic Worksheet

I have made one slight change to the range and it now looks like:

Code

Sub Print_PL()
Application.ScreenUpdating = False
Sheets("Pricing").Visible = True
Sheets("Pricing").PageSetup.PrintArea = "$A$1:$H$" & _
Sheets("Costing").Range("B65536").End(xlUp).Row
Sheets("Pricing").PrintOut
Sheets("Pricing").Visible = True
Application.ScreenUpdating = True
End Sub

As I had discovered that "A" (on costing) is used in another way and what I wanted to look at was actually in "B". I also had ended up keeping the sheet visible, as the end users didn't like not being able to see what they were printing.

I have attached a sample pricing and sample costing to give an idea what what I am attempting to explain. When I go to print, the print range may go to like 34 and give me two pages?? Now I do have in Page setup, to print line $1:$6 on each page (column headers), but there is not content.

Hope I am making sense

Beeker

Barrie Davidson · Jul 15th 2005

Re: Print Hidden Dynamic Worksheet

Quote from beeker

Now I do have in Page setup, to print line $1:$6 on each page (column headers)

Given that, I would change

Code

Sheets("Pricing").PageSetup.PrintArea = "$A$1:$H$" & _
    Sheets("Costing").Range("B65536").End(xlUp).Row

to read

Code

Sheets("Pricing").PageSetup.PrintArea = "$A$7:$H$" & _
    Sheets("Costing").Range("B65536").End(xlUp).Row

I am not clear on the rest of your problem. Based on the sample posted, you would only print to row 10 (determined by Costing) while you've got data in your Pricing workbook going to row 13. Can you clarify this for me?

Print Hidden Dynamic Worksheet

Participate now!

Share