+ Reply to Thread
Results 1 to 7 of 7

VBA for printing specific header, specific columns and cells to one page.

  1. #1
    Registered User
    Join Date
    04-06-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    27

    VBA for printing specific header, specific columns and cells to one page.

    Hello, i would like to know if there is a VB Code to take from sheet "store" some specific columns and cells an print them in one page.
    In my test file i would like to print the Header A1:A2 and then the columns A:B,D:J
    At the end of the page i want to print the cells L4:Q4.
    Because it will have more than 100 entries, i would like the header A1:A2 to be printed to everypage at the top of it.
    I supposed it can be done with a makro to copy all the cells-columns i need to print to another new sheet and after the print delete this new sheet, but i don't know how.
    Any help with that?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-06-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: VBA for printing specific header, specific columns and cells to one page.

    i found and used this code, but it prints these cells in 3 parts and in vertical orientation.
    A5:B9 in one paragraph
    D5:J9 bellow that
    and L4:O4 at the end.
    I would like to print the Headers A1:A2 in everypage at the top
    and then bellow that the cells A5:B9,D5:J9.
    at the end L4:O4.
    All of them in horizontal orientation

    Sub Test()
    Dim Destrange As Range
    Dim Smallrng As Range
    Dim Newsh As Worksheet
    Dim Ash As Worksheet
    Dim Lr As Long

    Application.ScreenUpdating = False

    Set Ash = ActiveSheet
    Set Newsh = Worksheets.Add
    Ash.Select

    Lr = 1


    For Each Smallrng In Ash.Range("A5:B9,D5:J9,L4:O4").Areas
    Smallrng.Copy
    Set Destrange = Newsh.Cells(Lr, 1)
    Destrange.PasteSpecial xlPasteValues
    Destrange.PasteSpecial xlPasteFormats
    Lr = Lr + Smallrng.Rows.Count
    Next Smallrng

    Newsh.Columns.AutoFit

    Newsh.PrintOut

    Application.DisplayAlerts = False
    Newsh.Delete
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

    End Sub

  3. #3
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VBA for printing specific header, specific columns and cells to one page.

    See attached file. Code below goes in your "store" object.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  4. #4
    Registered User
    Join Date
    04-06-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: VBA for printing specific header, specific columns and cells to one page.

    when i run the file, i saw it created a new sheet, but ther is an error in the code. The debugger show this row with yellow : Application.PrintCommunication = False

  5. #5
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VBA for printing specific header, specific columns and cells to one page.

    Quote Originally Posted by Chiefsotos View Post
    when i run the file, i saw it created a new sheet, but ther is an error in the code. The debugger show this row with yellow : Application.PrintCommunication = False
    Sorry - you're using 2007 ... just remove it and also the one at the bottom that = True.

  6. #6
    Registered User
    Join Date
    04-06-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: VBA for printing specific header, specific columns and cells to one page.

    thanks a lot man!!

  7. #7
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: VBA for printing specific header, specific columns and cells to one page.

    No problem - if you're happy, please mark the thread solved :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to copy the format of my header and my page preview to my specific sheet ?
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-24-2015, 08:56 AM
  2. [SOLVED] Fill Blank Cells in Columns with Specified Header with a Specific Date
    By uberathlete in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2014, 10:43 PM
  3. [SOLVED] Move to a worksheet continaing a value in a specific cell on a header page
    By robin hasling in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-15-2013, 01:16 PM
  4. Replies: 3
    Last Post: 05-01-2009, 11:39 AM
  5. Printing page with specific columns in excel
    By ctlqt12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2006, 08:14 PM
  6. [SOLVED] Determining and Printing a specific page
    By wnfisba in forum Excel General
    Replies: 3
    Last Post: 12-21-2005, 12:20 PM
  7. Replies: 3
    Last Post: 07-25-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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1