PDA

View Full Version : Print macro



nedy_03
03-07-2008, 01:40 AM
Hello,

Could someone help me with a little macro that does the following:
- in the column B4 from "Sheet1" I have this formula "=Sheet2!A1", and I would need a macro that prints the "sheet1", then changes the formula to ""=Sheet2!A2" and prints it, then to "=Sheet2!A3" and prints it, and so on until n.

Thx,
Nedy

Simon Lloyd
03-07-2008, 02:28 AM
So you only want to print single cells for the sheet? seems quite mad!

thomaspatton
03-07-2008, 05:15 AM
Something like this maybe?

Public Sub PrntConsecRw()
Dim ChngRw As Integer
With Worksheets("Sheet1")
.PrintOut
ChngRw = 1
Do Until ChngRw = 10
.Range("a1").Formula = "='Sheet2'!$A" & ChngRw
.PrintOut
ChngRw = ChngRw + 1
Loop
End With
End Sub

nedy_03
03-07-2008, 07:05 AM
Hello,

I've registred the code for the first value:
Sub Macro()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveCell.FormulaR1C1 = "=Sheet2!R[-2]C[-1]"
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B4").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-1]C[-1]"
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B4").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC[-1]"
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

It should stop at an "n" value, that I would digit.

Thx,
Nedy

thomaspatton
03-07-2008, 07:27 AM
The code above doesn't work for what you want? I tested it out using generic values in column a1:a15 on sheet 2 and it worked fine for me.

Just change :

Do Until ChngRw = 10
to
Do Until ChngRw = ***Place the total count you want here***

And then, of course these bolded areas:

With Worksheets("Sheet1")
.Range("a1".Formula = "='Sheet2'!$A" & ChngRw

... will have to be updated with your actual references.

This alleviates having to retype the same formula each time adding b1, b2, b3, etc, etc. By using ChngRw as a variable and calling it at the end of the formula it becomes the row number. everytime it prints out a page, it adds +1 to the row number.

nedy_03
03-07-2008, 07:32 AM
Thx for u're help .. I couldn't test it yet, as I didn't get home where I have a printer installed. I just wanted to make sure that u understood what I need, by register the code ..

I will try it tonight ... If it work for u I'm sure it will work for me too.

Thank you very much!!!

thomaspatton
03-07-2008, 07:42 AM
Thx for u're help .. I couldn't test it yet, as I didn't get home where I have a printer installed. I just wanted to make sure that u understood what I need, by register the code ..

I will try it tonight ... If it work for u I'm sure it will work for me too.

Thank you very much!!!

Try it now! that way, I can continue my bassackwards attempt at helping if this sin't what ya need.

Replace both
.PrintOut
with
MsgBox "Printing page - " & ChngRw, vbOKOnly, "Great Job!"

This will popup a message box giving you the number page it's printing instead of actually printing it. This way too, it will pause the Sub after every cell change so you can make sure it's getting what you want.

Simon Lloyd
03-07-2008, 07:51 AM
Nedy you have been a member at a couple of forums for quite sometime so you should know that crossposting is frowned on!

Cross posted here! (http://www.mrexcel.com/forum/showthread.php?t=307621)

thomaspatton
03-07-2008, 08:01 AM
NOOO!!

I'm aiding and abetting!

nedy_03
03-07-2008, 08:22 AM
Nedy you have been a member at a couple of forums for quite sometime so you should know that crossposting is frowned on!

Cross posted here! (http://www.mrexcel.com/forum/showthread.php?t=307621)

Ok, "cross posted" is a new word for me , now that I know what it means I won't do this thing again, or better said (now that I red the whole article) I'll do it right! :)

Regards,
Nedy