copy and printing ranges dynamically with a macro

[kelly mort]

Hello All,
I have this challenge here with me;
I am using a macro to print some work sheets and I am getting myself into a trap.

I used some sheets to lookup data from the data sheet then print them. So say I have 1000 rows of data and and I have to print 50 on a sheet, then I will insert 20 sheets to do the lookup. Then I have a macro that looks for those sheets and print them.

I did that for maximum data entry. But I may have less than 1000 sometimes. And when this happens then the macro prints only the sheets with data in them. It worked good with small data but now I think I need something better.

Any suggestions?
To avoid those number of sheets?

Can I use a macro to copy say first 50 then paste on a sheet and print then next 50 etc till the last data? Because I want all my data have headings when printed. Thanks in advance
Kelly

 

[kelly mort]

Okay thanks very much. It is working now. I have changed the activesheet to say Sheet1. And placed Sheet1 in the lr = Sheet1.cells...........
And for the lc

My aim is to call this macro and point it to sheet 1 always without making it the active sheet. Let me know if that's the right way to go.

Thanks again
Kelly

 

[jolivanes]

You should be ready to go.
If not, you'll get an error or your result is wrong. You're OK
Good luck

 

[kelly mort]

You should be ready to go.
If not, you'll get an error or your result is wrong. You're OK
Good luck

Hello,
I just observed another issue here:

The columns that it prints is more than what I want it print . So I want to enter my own number of columns. I don't need an InputBox. I need it placed in the script directly. Thanks

 

[jolivanes]

Change this line

.PageSetup.PrintArea = .Range("A1:A" & lr).Resize(, lc).Address    '<---- Column A to last used column

to

.PageSetup.PrintArea = .Range("A1:D" & lr).Address    '<---- Column A to Column D

Change the "D" in the above line to whatever Column you want.

 

[kelly mort]

Change this line

.PageSetup.PrintArea = .Range("A1:A" & lr).Resize(, lc).Address    '<---- Column A to last used column

to

.PageSetup.PrintArea = .Range("A1:D" & lr).Address    '<---- Column A to Column D

Change the "D" in the above line to whatever Column you want.

Okay.thanks

Has worked great!

Kelly

 

[kelly mort]

I have one more issue with this:

The sheet I am printing contains formulas. And I want the macro set page break for only rows that are not blank.

So say if from A2 to A50 are showing output then set page break for them and leave others with formula but no output.

To me this is tough but I am sure it is easy for someone here.

Thanks
kelly

 

[jolivanes]

Hide the rows that are blank.
Lots of code around for that.

 

[kelly mort]

Okay thanks.

I will hide them