Forum Discussion

Copper Contributor

Oct 17, 2022

VBA Calling a Macro - So basic and yet difficult for a novice!

I have a spreadsheet that has a variable number of rows of data and I want to select the area and set it as the new print area. After looking around I came across the following code which can run...

excel

Macros and VBA

CPBExcelnovice

Copper Contributor

Oct 19, 2022

Thanks for your replyHansVogelaar I'm sure that what I am doing is far from efficient, but I figure each step I take I learn a little bit more.

Yes, I finally worked out that a Macro is simply called using 'Call' Name_of_Macro .... Step 1 learned!

I have a Macro which copies cells from a worksheet into a new worksheet, however the number of rows varies. So I'm trying to find the range, set a border around the range and then set the print parameters i.e. set print range, set page to Landscape, fit all columns to 1 page. But I don't want to actually print the page, it just means that if my boss wants to all of the donkey work is done for him.

HansVogelaar

MVP

Oct 20, 2022

CPBExcelnovice

You only need to set the Print Area if you don't want to print all contents of a worksheet. See if this does what you want:

Sub SetPrintProperties()
    ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
    End With
    Application.PrintCommunication = True
End Sub