• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help Macro for Printing the worksheet.

I have written a vba for printing my worksheet . But unable to split my printing area to differnet pages as the rows doesn't fit in one A4 page.
with be greatful if somebody can help me . Thanks
 

Attachments

  • Stock Tracking List V 3.3.xlsm
    71.3 KB · Views: 2
Vipin Aeri
Have You tried to set
a) PrintArea?
b) pages orientation?
c) scaling?
... the whole sheet can fit in one page ... but it could be hard to read.
What kind of print do You have an idea to get?
 
Vipin Aeri
Have You tried to set
a) PrintArea?
b) pages orientation?
c) scaling?
... the whole sheet can fit in one page ... but it could be hard to read.
What kind of print do You have an idea to get?
Yes I tried
and Manually I can print .But would like to do with VBA. e.g from Range E5: E35 on one Sheet
and then again 30 rows on other sheet with headers
 
Vipin Aeri
... tried,
but Your code did not have any of those feature ... why?
Have You tried to use Macro Recorder to get those routines?
... range E5:E35 is one column and it has 31 rows.
 
Vipin Aeri
This file has one sample - how to do those Your named settings for that protected sheet.
There are those Your two buttons ... which You maybe have set to print too ... of course, those could be printable too.
You could test it - by - basic Print.
Yes, those has done manually, because those would be fixed.
If something would do with macro ... maybe to Set Print Area ( as it should to do by code ).
 

Attachments

  • Stock Tracking List V 3.3.xlsm
    65.8 KB · Views: 2
Vipin Aeri
This file has one sample - how to do those Your named settings for that protected sheet.
There are those Your two buttons ... which You maybe have set to print too ... of course, those could be printable too.
You could test it - by - basic Print.
Yes, those has done manually, because those would be fixed.
If something would do with macro ... maybe to Set Print Area ( as it should to do by code ).
I wrote a lengthy VBA code and achieved my Goal. But if you can look in my code and suggest some corrections. I will be very thankful. I am not so good in coding.

My Code:-

>>> use code - tags <<<
Code:
Sub printtasks3()
Dim r1 As Range, r2 As Range, r3 As Range

Set r1 = Range("E5:R35")
Set r2 = Range("E36:R66")
Set r3 = Range("E67:R100")

r1.EntireColumn.Hidden = False
r2.EntireColumn.Hidden = False
r3.EntireColumn.Hidden = False


Worksheets("Stock Tracking Template").Activate
ActiveSheet.PageSetup.PrintTitleRows = "$5:$5"
ActiveSheet.PageSetup.PrintArea = r1.Address & "," & r2.Address & "," & r3.Address
ActiveSheet.PageSetup.PaperSize = xlPaperA4
ActiveSheet.PageSetup.FirstPageNumber = xlAutomatic
ActiveSheet.PageSetup.Order = xlDownThenOver
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.LeftMargin = Application.CentimetersToPoints(0)
ActiveSheet.PageSetup.RightMargin = Application.CentimetersToPoints(0)
ActiveSheet.PageSetup.TopMargin = Application.CentimetersToPoints(0)
ActiveSheet.PageSetup.BottomMargin = Application.CentimetersToPoints(0)
ActiveSheet.PageSetup.HeaderMargin = Application.CentimetersToPoints(0)
ActiveSheet.PageSetup.FooterMargin = Application.CentimetersToPoints(0)
ActiveSheet.PageSetup.CenterHorizontally = True
ActiveSheet.PageSetup.CenterVertically = False
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = False
ActiveSheet.PrintPreview
End Sub
 
Last edited by a moderator:
Vipin Aeri
You seems to able to copy & paste my reply.
... to suggest some correction ... okay.
You could do as I wrote and use
Code:
Sub Print_Out()
    ActiveSheet.PrintOut
End Sub
 
try:
Code:
Sub printtasks3()
With Worksheets("Stock Tracking Template")
  .Range("E:R").EntireColumn.Hidden = False
  With .PageSetup
    .PrintTitleRows = "$5:$5"
    .PrintArea = "E5:R100"
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .Orientation = xlLandscape
    .LeftMargin = 0
    .RightMargin = 0
    .TopMargin = 0
    .BottomMargin = 0
    .HeaderMargin = 0
    .FooterMargin = 0
    .CenterHorizontally = True
    .CenterVertically = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
  .HPageBreaks.Add Before:=.Range("A36")
  .HPageBreaks.Add Before:=.Range("A67")
  .PrintPreview
End With
End Sub
 
try:
Code:
Sub printtasks3()
With Worksheets("Stock Tracking Template")
  .Range("E:R").EntireColumn.Hidden = False
  With .PageSetup
    .PrintTitleRows = "$5:$5"
    .PrintArea = "E5:R100"
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .Orientation = xlLandscape
    .LeftMargin = 0
    .RightMargin = 0
    .TopMargin = 0
    .BottomMargin = 0
    .HeaderMargin = 0
    .FooterMargin = 0
    .CenterHorizontally = True
    .CenterVertically = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
  .HPageBreaks.Add Before:=.Range("A36")
  .HPageBreaks.Add Before:=.Range("A67")
  .PrintPreview
End With
End Sub
thanks, it works perfectly, and it short too.
 
Back
Top