Macro to Print Update and Print Sheet

**erock24** · 11-21-2007, 06:34 PM

I have a workbook where 1 sheet has a list of numbers starting at b12 on down (list gets bigger and smaller) and another sheet has a bunch of lookup formulas on it. I key in the numbers 1 by 1 from the 1st sheet into cell N1 on the sheet with the formulas. The formulas then input a bunch of info creating an invoice and I print the page. Then key the next number and so on.

Is it possible to have a macro enter the numbers for me 1 by 1 printing each page after it enters?? This would be a tremendous time saver.

Thank you very much for your time and help.

**Trebor76** · 11-21-2007, 07:20 PM

Hi there,

Let me know if the following does the trick - just change the reference to Sheet1 to the tab name where the invoice number(s) are listed and the references to Sheet2 to the invoice tab.

HTH

Robert

Please Login or Register  to view this content.

**erock24** · 11-22-2007, 02:09 PM

I tested it but a different way. Turns out I need to be able to print sometimes and other times just keep copies as different sheet tabs.

So, I set this up but... it only did the first one and stopped. Seems that the offset command affected cell n1 on the invoice sheet because cell n2 was the active cell on that page when the macro stopped. I don't know how to get the offset to work on the "CL lookup" page in the desired range (b12:down) Any Ideas???

Please Login or Register  to view this content.

This is 2nd priority, but it would be nice to somehow choose if I want to just print or If I want to have an active copy of each invoice. And if it is doable, if I choose active copy, then maybe an option at end to print active invoices.

**Trebor76** · 11-22-2007, 06:52 PM

Hi there,

As I don't have any data, I've created the following code based on the assumption that the new (active) invoice tabs are the unique invoice numbers (i.e. the value in cell I14 of the Invoice tab) and put either a "C" (for create) or "P" (for print) flag next to the invoice numbers (Column C) in the "CL lookup" tab.

Please Login or Register  to view this content.

To be honest (in my opinion) I'd actually do this in Access as there will invariably be issues with the number of sheet tabs, duplicate tab names, tab ordering and processing time as the number of invoices grow, which though mostly can be overcome with additional code, unnecessarily complicates matters.

HTH

Robert

**erock24** · 11-25-2007, 01:28 PM

Thank you very much for your help and advice.

I tested code and,
One weird thing is that I tested it with only having "C"s and it created them, then asked to print, when I hit yes to print it printed them fine, but also errored out highlighting this:

Please Login or Register  to view this content.

The only other issue with the code is when I only have "P", I then don't need the second part to run.
But it still works and is a time saver.

**Trebor76** · 11-25-2007, 07:01 PM

Hi there,

It's hard to fully test without actual data, but see if this meets all your needs.

Regards,

Robert

Please Login or Register  to view this content.

**erock24** · 11-26-2007, 11:00 AM

Thanks for the additional code. I moved.

Please Login or Register  to view this content.

From the print section to the create section and I adjusted the columns to fit my needs. I attached a test copy of the file. The only problem I have is when I have a P and a C in Column N and choose to print the C. It errors out. But If I have all C and print it's fine. All P its fine. P and C but don't print C its fine.
Any Ideas??

Thank you for your time and help.

**erock24** · 11-26-2007, 11:10 AM

I added this to the code and it seems to have solved the problem

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Macro to Print Update and Print Sheet

LinkBack

Thread Tools

Rate This Thread

Display

Macro to Print Update and Print Sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions