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

vletm

Excel Ninja
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

New Member
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
 

vletm

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

vletm

Excel Ninja
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

Vipin Aeri

New Member
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:

vletm

Excel Ninja
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
 

p45cal

Well-Known Member
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
 

Vipin Aeri

New Member
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.
 
Top