A simple printing pages depending on cell value

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
I hope someone can help because this is driving me absolutely bonkers. This should be something so easy yet....

I am trying to print a number of pages depending on a cell value which is a value of a formulae

Sub PRINT_GENERIC()
Code:
Sheets("GENERIC").Select

a = Range("Z1").Value
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=a, Copies:=1
Sheets("MAINSHEET").Select


End Sub



The whole worksheet has page breaks in it to make 40 pages. Every time the above macro is run it will print 40 pages regardless of the value of (a).

What am I doing wrong?

Thanks
 
Last edited:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I'm curious if you can try this and let me know what happens...

Code:
Sub PrintMe()
With Worksheets("Generic")
    MsgBox "I should be printing pages 1 to " & .Range("Z1").Value, vbOKOnly + vbInformation
    .PrintOut From:=1, To:=.Range("Z1").Value, Copies:=1
End With
Worksheets("MAINSHEET").Select
End Sub

The biggest question is what the messagebox says... how many sheets does it say vs how many do you get?
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
Ken

Thank you for your reply.

It comes back with the correct value whether its 1 page, 2 etc. but then will still print the full 40 pages.

I don't know if this information will help but if I set my print to PDF its works fine and generates a PDF for the correct number of pages, if I set it to printer it prints 40 pages.

All of the worksheets have been set up beforehand with the print area to equate to 40 pages.

I don't think I have ever been so frustrated doing a spreadsheet, I have spent hours on this trying to figure it out.

Regards





I'm curious if you can try this and let me know what happens...

Code:
Sub PrintMe()
With Worksheets("Generic")
    MsgBox "I should be printing pages 1 to " & .Range("Z1").Value, vbOKOnly + vbInformation
    .PrintOut From:=1, To:=.Range("Z1").Value, Copies:=1
End With
Worksheets("MAINSHEET").Select
End Sub

The biggest question is what the messagebox says... how many sheets does it say vs how many do you get?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I don't suppose that there is any way that you could share the spreadsheet with us, is there? I'd be curious to have a look at the specific sheet to see if I can see anything. It doesn't make a lot of sense that it would work for PDF but not for printing.
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
I don't suppose that there is any way that you could share the spreadsheet with us, is there? I'd be curious to have a look at the specific sheet to see if I can see anything. It doesn't make a lot of sense that it would work for PDF but not for printing.

Ken

I have attached the spreadsheet but I had to delete alot of it due to the file upload limitation. This basically shows 1 sheet and the print process. I hope you can make sense of it.

Regards
 

Attachments

  • comsheetapp.xlsm
    287.2 KB · Views: 151

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I made two changes to this before I tried it:
1) Added "Option Explicit" (no quotes) as the first line. (Forces you to declare your variables, which is a programming best practice.)
2) Inserted the line "Dim response As Variant" as the first line of your "Sub Clear_Contents" procedure, as that variable was not declared

Neither of these changes should have affected the way this marcro runs.

I then ran your PRINT_GENERIC routine here. It popped up a message saying that it was printing 1 of 40, but only 1 page atually printed. Are you actually getting the full 40 phyiscally printing?

(I ran this in Office 2010, but I can't see that making any difference.)
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
I made two changes to this before I tried it:
1) Added "Option Explicit" (no quotes) as the first line. (Forces you to declare your variables, which is a programming best practice.)
2) Inserted the line "Dim response As Variant" as the first line of your "Sub Clear_Contents" procedure, as that variable was not declared

Neither of these changes should have affected the way this marcro runs.

I then ran your PRINT_GENERIC routine here. It popped up a message saying that it was printing 1 of 40, but only 1 page atually printed. Are you actually getting the full 40 phyiscally printing?

(I ran this in Office 2010, but I can't see that making any difference.)


Ken

I have just tested what you said and you are indeed correct, even though it says that its printing 40/80 or even 200 pages it will still only print the variable of Z1. This is very strange indeed but in any case does work correctly.

Would you be able to help with 1 last problem.

You will see that when entering the data onto the 40 pages that there is a break in the data due to page headings. I am copying this data entered to another worksheet an of course there is spaces within the datasheet that need to be deleted.

This that I did is not working.

Sub RemoveEmptyRows()
On Error Resume Next
Range("II886:JG927").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Can you suggest a way of deleting all empt cells within any size range of cells.

Many thanks for your help

Regards

Antony
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I can, Antony. :)

What I do when I get a non-contiguous range of data (a table with blank rows), is this:
  • I figure out what my last column of data is
  • I run a formula down the next column to the very last row in the data table, which adds one to the number in the cell above
  • I copy that, then pastespecial->values
  • I then sort the table by the first column
  • Use Autofilter to filter out the records you want to delete
  • Delete the visible cells
  • Turn off the autofilter
  • Resort the table by that last column we inserted (to put it back in the original order)
  • Delete the last column

Now, that might sound like a lot, but you can pretty much record the entire thing to get the syntax, then tweak it to be a bit more dynamic.

I'm not going to be able to get back to this for about 24 hours, but if you can start working on that part, I can hlep you tweak it or work through it later. (I'd like you to give it a go to begin with and post the code though, so I can see where the data will end up and how big it will be.)

Try these manual navigation steps before you start to record the macro:
  • End + Right Arrow will move you to the last column of data in your table
  • Arrow over to the right again, and End + Down Arrow will move you to the last row in the worksheet
  • Left arrow will move you back to the last colum
  • End + Up Arrow will move you to the last row of your data
  • Right Arrow will take you to the first blank column
  • Shift + Up Arrow will select the blank column from the last row to the first row
  • Still holding down Shift, press Down Arrow once to shorten that range
  • Enter ={Up Arrow}+1 and press CTRL+Enter (that will enter that formula in all selected cells)
  • Press CTRL+C to copy
  • Press CTRL+ALT+V -> V to paste values
  • Now you're ready to sort and apply the autofilter

Hopefully that makes some sense. :)
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
I can, Antony. :)

What I do when I get a non-contiguous range of data (a table with blank rows), is this:
  • I figure out what my last column of data is
  • I run a formula down the next column to the very last row in the data table, which adds one to the number in the cell above
  • I copy that, then pastespecial->values
  • I then sort the table by the first column
  • Use Autofilter to filter out the records you want to delete
  • Delete the visible cells
  • Turn off the autofilter
  • Resort the table by that last column we inserted (to put it back in the original order)
  • Delete the last column

Now, that might sound like a lot, but you can pretty much record the entire thing to get the syntax, then tweak it to be a bit more dynamic.

I'm not going to be able to get back to this for about 24 hours, but if you can start working on that part, I can hlep you tweak it or work through it later. (I'd like you to give it a go to begin with and post the code though, so I can see where the data will end up and how big it will be.)

Try these manual navigation steps before you start to record the macro:
  • End + Right Arrow will move you to the last column of data in your table
  • Arrow over to the right again, and End + Down Arrow will move you to the last row in the worksheet
  • Left arrow will move you back to the last colum
  • End + Up Arrow will move you to the last row of your data
  • Right Arrow will take you to the first blank column
  • Shift + Up Arrow will select the blank column from the last row to the first row
  • Still holding down Shift, press Down Arrow once to shorten that range
  • Enter ={Up Arrow}+1 and press CTRL+Enter (that will enter that formula in all selected cells)
  • Press CTRL+C to copy
  • Press CTRL+ALT+V -> V to paste values
  • Now you're ready to sort and apply the autofilter

Hopefully that makes some sense. :)

Ken I have only been using excel for the last week so alot of this is very new to me. I have attempted to do what you said but it did not work out for me and I have to admit I did not entirely understand line 2 and 3.

I have attached what I have done.

Looking at the whole program I have done now I should not have done 40 pages with 40 headers and there probably is someway of having 600 lines of data with 1 header and putting that header of each of the 40 pages when it prints out.

I am learning as fast as I can but after a couple of hours the frustration is setting in.

I have sorted the data but the rows are not deleting.

I have attached the file that I was using to test this.

Regards
 

Attachments

  • comsheetapp.xlsm
    287.2 KB · Views: 87

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
Ken I have only been using excel for the last week so alot of this is very new to me. I have attempted to do what you said but it did not work out for me and I have to admit I did not entirely understand line 2 and 3.

I have attached what I have done.

Looking at the whole program I have done now I should not have done 40 pages with 40 headers and there probably is someway of having 600 lines of data with 1 header and putting that header of each of the 40 pages when it prints out.

I am learning as fast as I can but after a couple of hours the frustration is setting in.

I have sorted the data but the rows are not deleting.

I have attached the file that I was using to test this.

Regards

Ken

I think I have achieved what I needed, I don't think it was 100% the correct way but it did get the result. Would you be able to tell me how to copy a range of cells to the clipboard? I have now deleted the cells and I want to copy the range that has data to the clipboard to paste into another spreadsheet.

Many thanks

Antony
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Antony,

To copy from one workbook to another, you can use:
Code:
Workbooks("Source Workbook Name.xlsx").Worksheets("My Sheet").Range("A1:B15").Copy
Workbooks("Destination Workbook name.xlsx").Worksheets("Target Sheet").Range("G5").PasteSpecial Paste:=xlPasteAll 'or xlPasteValues

That is written in the browser and untested, but it looks right to my sleepy eyes.

FYI, I may not be online until after the weekend, as we're away celebrating a family birthday. Any chances I do get to check in will be sporadic.
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
Hi Antony,

To copy from one workbook to another, you can use:
Code:
Workbooks("Source Workbook Name.xlsx").Worksheets("My Sheet").Range("A1:B15").Copy
Workbooks("Destination Workbook name.xlsx").Worksheets("Target Sheet").Range("G5").PasteSpecial Paste:=xlPasteAll 'or xlPasteValues

That is written in the browser and untested, but it looks right to my sleepy eyes.

FYI, I may not be online until after the weekend, as we're away celebrating a family birthday. Any chances I do get to check in will be sporadic.

Ken

I spoke too soon, I thought last night at 2.00am I had it completed and in the morning I am still getting errors.

Code:
Sub deleterows()


Sheets("DATASHEET").Select
    Application.ScreenUpdating = False
    Cells.Select
    Range("B1").Activate
    Selection.EntireColumn.Hidden = False
    Range("A4:Z1077").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$Z$1077").AutoFilter Field:=2, Criteria1:="="
    Rows("20:1077").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-15
    Range("B65").Select
    ActiveSheet.Range("$A$4:$Z$64").AutoFilter Field:=2
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    Columns("N:N").Select
    Selection.EntireColumn.Hidden = True
    Columns("P:P").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Columns("T:Y").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 2
    Range("B5").Select
With Range("b5")
    Range(.Cells(1, 1), .End(xlDown).Cells(1, 25)).Copy
Range("B5").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  ActiveSheet.Paste
End With
With Range("b2")
    Range(.Cells(1, 1), .End(xlDown).Cells(1, 25)).Copy
End With
Sheets("ACCOUNTSPAGE").Select
End Sub

it gets to this part and falls down

Code:
Selection.EntireColumn.Hidden = False

It says unable to set the hidden property of the range class. I think this is due to the page being protected. I have tried to enter this information at various places but still cannot get it to work.

Code:
With Sheets("DATASHEET")
    .unprotect Password:="password"
    CODE

    .protect Password:="password"
    End With

The worse part is I sent it to my boss as completed because I thought it was.

I would really appreciate any help.
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
Ken

I have spent alot of time learning over the last couple of days and I am picking things up pretty fast. I have got everything working how I want it now except one thing the protection.

Everything works fine until I put the protection on and then alot of the macros do not work due to hiding columns and copying and pasting etc, the protection stops this from happening.

I need some kind of protection that I can switch on and switch off easily but still allow me to run the macros needed.

I have tried this

Code:
Sub ProtectAll()
     Application.ScreenUpdating = False
    Dim wSheet          As Worksheet
    Dim Pwd             As String
     
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.protect Password:=Pwd
    Next wSheet
    Application.OnKey "{ESC}", "BACKTOMAINPAGE"
End Sub
 Sub UnProtectAll()
     Application.ScreenUpdating = False
    Dim wSheet          As Worksheet
    Dim Pwd             As String
     
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    On Error Resume Next
    For Each wSheet In Worksheets
        wSheet.unprotect Password:=Pwd
    Next wSheet
    If Err <> 0 Then
        MsgBox "You have entered an incorect password. All worksheets could not " & _
        "be unprotected.", vbCritical, "Incorect Password"
    End If
    On Error GoTo 0
    Application.OnKey "{ESC}"
End Sub

But when I attempt to run a macro it says the password is not correct. I feel that I have too many of these dotted everywhere which are not working

With Sheets("sheet1")
.unprotect Password:="password"
content
.protect Password:="password"

It would be great if you could let me know a simpler way of protecting the sheets but allowing all the macros to run without having to turn them off and on each time a macro needs to be run.

Thanks for you help

Regards
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there,

Sorry for the late reply. I was away all weekend with family.

I'd modify the first routine to put the password there. Why give your users an option to record it?
Code:
Sub ProtectAll()
    Application.ScreenUpdating = False
    Dim wSheet          As Worksheet
    Dim Pwd             As String
     
    Pwd = "MyPassword"
    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd, userinterfaceonly:=True
    Next wSheet
    Application.OnKey "{ESC}", "BACKTOMAINPAGE"
End Sub

You'll also notice that I added the "userinterfaceonly:=True" keywords to the protection routine. This will protect the worksheets while still letting macros run on them.

The thing that sucks though, is that you need to re-apply that every time the workbook is reopened. To do that, we use a Workbook_Open event (which goes in the ThisWorkbook module):
Code:
Private Sub Workbook_Open()
    Call ProtectAll
    
End Sub

(Just make sure you unprotect all the sheets before you run this and set the pwd string to what you want.)
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
Hi there,

Sorry for the late reply. I was away all weekend with family.

I'd modify the first routine to put the password there. Why give your users an option to record it?
Code:
Sub ProtectAll()
    Application.ScreenUpdating = False
    Dim wSheet          As Worksheet
    Dim Pwd             As String
     
    Pwd = "MyPassword"
    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd, userinterfaceonly:=True
    Next wSheet
    Application.OnKey "{ESC}", "BACKTOMAINPAGE"
End Sub

You'll also notice that I added the "userinterfaceonly:=True" keywords to the protection routine. This will protect the worksheets while still letting macros run on them.

The thing that sucks though, is that you need to re-apply that every time the workbook is reopened. To do that, we use a Workbook_Open event (which goes in the ThisWorkbook module):
Code:
Private Sub Workbook_Open()
    Call ProtectAll
    
End Sub

(Just make sure you unprotect all the sheets before you run this and set the pwd string to what you want.)

Ken

Thanks for that, the last think I am attempting do is to find the end column and end row of data but when there is hidden columns.

I tried what you said but it keeps stopping when it reaches a column with no data in it and the same with the rows and just jumps to the last row in the worksheet 10k+

Any help would be great

Antony
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
Antony,

You can use the method Ken mentioned to find the last row or column, even if there are hidden columns.
try using this.


Code:
Dim lastRow As Long
Dim lastCol As Long

with ActiveSheet
   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Change the "A" to whatever column you want to use to find the last row in.  
   lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column 'Change the 1 to whatever row you want to find the last column in.
end with
 

antonywalsh

New member
Joined
May 8, 2012
Messages
10
Reaction score
0
Points
0
Simi

I tried what Ken said but 'end + right arrow key just took me to the next cell and not the end cell with data.

I have cells filled from a-z

Columns a,c,n,p and t-y will always be empty

I need to copy from A1 to Z? whatever the data goes to and then I need to allow the user to paste this data into another spreadsheet.

Can you help?

regards
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I have only started using the .end feature in my code, but hopefully can break it down for you.

If you just it end + right arrow, that takes you to the next cell to the right that has data. This is useful to skip the blank cells when moving to the right but not very helpful to find the last column of data.

Keeping that in mind, if we pick a cell way to the right of the data entered and use end + left arrow, it will take us to the first cell (moving left) that has data in it. This should be the furthest column to the right of your data area.

The .columns.count returns us a value of 16,384 which I believe is the default number of columns on a new worksheet.

lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
So in the above line of code, we start on row 1, column 16,384. Then we move to the left to the first cell that contains data. The column number we just arrived at with data is then stored in the lastCol variable.

You can then use this variable when you are selecting the data you want to copy.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Antony,

Give this a try:
Code:
    With ActiveSheet
        .Range("A1:Z" & .Range("Z1").End(xlDown).Row).Copy
        Worksheets("MyTarget").Range("A1").PasteSpecial Paste:=xlPasteValues
    End With
 
Top