A simple printing pages depending on cell value

[antonywalsh]

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()

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

[Ken Puls]

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

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]

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...

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]

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]

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

[Ken Puls]

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]

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]

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]

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

[antonywalsh]

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