+ Reply to Thread
Results 1 to 13 of 13

Print Specific Areas

  1. 03-02-2015, 09:23 AM #1
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Question Print Specific Areas

    Hi to everyone.

    First of all thank you for your valuable time.

    I have a workbook with 10 sheets.
    In each sheet i have specific print areas. Two areas per sheet (A & B).


    I want to find away in order to print all A areas from the selected sheets, or B areas.
    Areas are not the same. In one sheet could be 10 rows and 5 columns, and in another sheet the area could be totally different.

    How can i use the VBA in order to print all A areas or B areas?

    Thanks again
    Reply With QuoteRegister To Reply
  2. 03-02-2015, 10:44 AM #2
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Print Specific Areas

    How are the areas described: Are they always the same?
    A short example to see the sheets will help
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  3. 03-02-2015, 10:56 AM #3
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Re: Print Specific Areas

    The areas are not the same.
    For example Sheet 1 : (A) A1-Z10 (B) A100 - Z120
    Sheet 2 : (A) A1-C20 (B) A100 - C120
    Sheet 3 : (A) A1-D7 (B) A100 - D127

    I do not mind if i have to declare in the code each area per sheet. i just need an example of how to do it.
    And also what i need is when to run a macro to have an option to declare if i want to print areas (A) or (B), as those declared per sheet in the code.
    Reply With QuoteRegister To Reply
  4. 03-02-2015, 12:26 PM #4
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Print Specific Areas

    And you want to print all areas A/B for all sheets in one shot ?
    Reply With QuoteRegister To Reply
  5. 03-02-2015, 06:15 PM #5
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Print Specific Areas

    See how the file attached can help
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-03-2015, 10:08 AM #6
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Re: Print Specific Areas

    I want to print either areas A or B. Not both of them.
    I have viewed the filed but i couldn't identify the way of how i can use it.
    Reply With QuoteRegister To Reply
  7. 03-03-2015, 11:07 AM #7
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Print Specific Areas

    See attached an update.
    Do Control + g to launch the macro
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-03-2015, 11:19 AM #8
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Re: Print Specific Areas

    S O U P E R

    many many thanks
    Reply With QuoteRegister To Reply
  9. 03-03-2015, 11:24 AM #9
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Print Specific Areas

    Great I'm please
    Reply With QuoteRegister To Reply
  10. 03-04-2015, 09:26 AM #10
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Re: Print Specific Areas

    One more question.
    I add all pages with print areas but i am getting an error. "Subscript out of range".
    Can anyone assist on this error?
    I am pasting the code below.


    Sub PrintAreas()
    Dim PrRgAT As Range
    Dim PrRgBT As Range
    Dim MyChoice
    Dim WSLst, PrALst, PrBLst
    Dim WSStg As String, PrAAddStg As String, PrBAddStg As String
    Dim I As Integer

    WSStg = "N6_Aff_EntityGroup / IFRS7 / IFRS7 Liab / PnL / PnL Quarter / Balance Sheet / EQ_NT_R / Equity Group / Equity Entity / Cash Flow / IFRS13 / 42B_GroupR / A01 / A03 / A04 / DefTax / Segment01 / Segment02 / A06 / A11 / A12 / A14 / A16 / A18 / A09 / A17 / A19 / L04 / L08 / L11a / L11b / L11c / L11d / L12 / L13 / L14 / L15 / L20 / PL02 / PL04 / PL07 / PL10 / PL11 / PL14 / N6_30 / N6_31 / DisOper / N6_35 / N6_39 / N6_40B / N6_40A_R / PL01" ' the LIST of SHEETS
    PrAAddStg = "A1:D43 / A1:G16 / A1:F45 / A1:E53 / A1:J53 / A1:E58 / A1:E21 / A1:K39 / A1:I26 / A1:F48 / A1:E50 / A1:C16 / A01:F72 / A1:H27 / A1:F73 / A1:H44 / A1:G37 / A1:H21 / A1:C9 / A1:E8 / A1:C12 / A1:E24 / A1:E11 / A1:E5 / A1:C12 / A1:E9 / A1:E11 / A1:I42 / A1:E19 / A1:G37 / A1:G26 / A1:C24 / A1:C26 / A1:E54 / A1:F37 / A1:E9 / A1:E7 / A1:E10 / A1:E18 / A1:E38 / A1:E23 / A1:E24 / A1:E12 / A1:E29 / A1:E18 / A1:E39 / A1:C25 / A1:E7 / A1:E6 / A1:E16 / A1:E45 / A1:E13" ' the LIST of ADDRESS for PRINT AREAS for A
    PrBAddStg = "A100:D141 / A100:G115 / A100:F145 / A100:E152 / A100:J152 / A100:E157 / A100:E120 A100:K146 / A100:I125 / A100:F147 / A100:E149 / A100:C116 / A100:F172 / A100:H126 / A100:F174 / A100:H142 / A100:G138 / A100:H121 / A100:C108 / A100:E107 / A100:C112 / A100:E124 / A100:E111 / A100:E105 / A100:C112 / A100:E109 / A100:E111 / A100:I142 / A100:E119 / A100:G137 / A100:G126 / A100:C124 / A100:C126 / A100:E154 / A100:F137 / A100:E109 / A100:E107 / A100:E110 / A1:E118 / A100:E138 / A100:E123 / A100:E124 / A100:E112 / A100:E129 / A100:E118 / A100:E139 / A100:C125 / A100:E107 / A100:E106 / A100:E116 / A100:E145 / A100:E113" ' the LIST of ADDRESS for PRINT AREAS for B

    WSLst = Split(WSStg, "/")
    PrALst = Split(PrAAddStg, "/")
    PrBLst = Split(PrBAddStg, "/")

    For I = 0 To UBound(WSLst, 1)
    WSLst(I) = Trim(WSLst(I))
    PrALst(I) = Trim(PrALst(I))
    PrBLst(I) = Trim(PrBLst(I))
    Next I

    MyChoice = Application.InputBox("Select Areas to Print A or B", Type:=2)
    Select Case MyChoice
    Case "A"
    For I = 0 To UBound(WSLst, 1)
    Sheets(WSLst(I)).PageSetup.PrintArea = PrALst(I)
    Next I
    Case "B"
    For I = 0 To UBound(WSLst, 1)
    Sheets(WSLst(I)).PageSetup.PrintArea = PrBLst(I)
    Next I
    Case Else
    Exit Sub
    End Select

    Sheets(WSLst).PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    End Sub
    Reply With QuoteRegister To Reply
  11. 03-04-2015, 09:29 AM #11
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Re: Print Specific Areas

    One more question.
    I add all pages with print areas but i am getting an error. "Subscript out of range".
    Can anyone assist on this error?
    I am pasting the code below.


    Sub PrintAreas()
    Dim PrRgAT As Range
    Dim PrRgBT As Range
    Dim MyChoice
    Dim WSLst, PrALst, PrBLst
    Dim WSStg As String, PrAAddStg As String, PrBAddStg As String
    Dim I As Integer

    WSStg = "N6_Aff_EntityGroup / IFRS7 / IFRS7 Liab / PnL / PnL Quarter / Balance Sheet / EQ_NT_R / Equity Group / Equity Entity / Cash Flow / IFRS13 / 42B_GroupR / A01 / A03 / A04 / DefTax / Segment01 / Segment02 / A06 / A11 / A12 / A14 / A16 / A18 / A09 / A17 / A19 / L04 / L08 / L11a / L11b / L11c / L11d / L12 / L13 / L14 / L15 / L20 / PL02 / PL04 / PL07 / PL10 / PL11 / PL14 / N6_30 / N6_31 / DisOper / N6_35 / N6_39 / N6_40B / N6_40A_R / PL01" ' the LIST of SHEETS
    PrAAddStg = "A1:D43 / A1:G16 / A1:F45 / A1:E53 / A1:J53 / A1:E58 / A1:E21 / A1:K39 / A1:I26 / A1:F48 / A1:E50 / A1:C16 / A01:F72 / A1:H27 / A1:F73 / A1:H44 / A1:G37 / A1:H21 / A1:C9 / A1:E8 / A1:C12 / A1:E24 / A1:E11 / A1:E5 / A1:C12 / A1:E9 / A1:E11 / A1:I42 / A1:E19 / A1:G37 / A1:G26 / A1:C24 / A1:C26 / A1:E54 / A1:F37 / A1:E9 / A1:E7 / A1:E10 / A1:E18 / A1:E38 / A1:E23 / A1:E24 / A1:E12 / A1:E29 / A1:E18 / A1:E39 / A1:C25 / A1:E7 / A1:E6 / A1:E16 / A1:E45 / A1:E13" ' the LIST of ADDRESS for PRINT AREAS for A
    PrBAddStg = "A100:D141 / A100:G115 / A100:F145 / A100:E152 / A100:J152 / A100:E157 / A100:E120 A100:K146 / A100:I125 / A100:F147 / A100:E149 / A100:C116 / A100:F172 / A100:H126 / A100:F174 / A100:H142 / A100:G138 / A100:H121 / A100:C108 / A100:E107 / A100:C112 / A100:E124 / A100:E111 / A100:E105 / A100:C112 / A100:E109 / A100:E111 / A100:I142 / A100:E119 / A100:G137 / A100:G126 / A100:C124 / A100:C126 / A100:E154 / A100:F137 / A100:E109 / A100:E107 / A100:E110 / A1:E118 / A100:E138 / A100:E123 / A100:E124 / A100:E112 / A100:E129 / A100:E118 / A100:E139 / A100:C125 / A100:E107 / A100:E106 / A100:E116 / A100:E145 / A100:E113" ' the LIST of ADDRESS for PRINT AREAS for B

    WSLst = Split(WSStg, "/")
    PrALst = Split(PrAAddStg, "/")
    PrBLst = Split(PrBAddStg, "/")

    For I = 0 To UBound(WSLst, 1)
    WSLst(I) = Trim(WSLst(I))
    PrALst(I) = Trim(PrALst(I))
    PrBLst(I) = Trim(PrBLst(I))
    Next I

    MyChoice = Application.InputBox("Select Areas to Print A or B", Type:=2)
    Select Case MyChoice
    Case "A"
    For I = 0 To UBound(WSLst, 1)
    Sheets(WSLst(I)).PageSetup.PrintArea = PrALst(I)
    Next I
    Case "B"
    For I = 0 To UBound(WSLst, 1)
    Sheets(WSLst(I)).PageSetup.PrintArea = PrBLst(I)
    Next I
    Case Else
    Exit Sub
    End Select

    Sheets(WSLst).PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    End Sub
    Reply With QuoteRegister To Reply
  12. 03-04-2015, 11:23 AM #12
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Print Specific Areas

    Is missing a "/" in areas for B
    A100:E120 A100:K146

    We could see for an easier way to prepare the data
    Reply With QuoteRegister To Reply
  13. 03-05-2015, 07:10 AM #13
    donk04
    donk04 is offline
    Registered User
    Join Date
    03-02-2015
    Location
    Hellas
    MS-Off Ver
    2010
    Posts
    36

    Re: Print Specific Areas

    I fix it .
    Thank you again for your valuable time
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to print multiple print areas on one page.
    By morangeman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2015, 12:13 PM
  2. Create buttons to print defined print areas
    By nwpassage in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2015, 11:17 PM
  3. Print macro that prints certain areas in specific color
    By MLarsen87 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-03-2014, 08:16 AM
  4. VBA code to print several print areas in one worksheet
    By ABSTRAKTUS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2010, 03:24 PM
  5. [SOLVED] printing multiple print areas with a print dialog box
    By LHaro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1