Solved

VBA to print non-contiguous columns in Excel worksheet

Posted on 2010-09-07
752 Views
Last Modified: 2012-05-10
Hi, Experts!
I have a worksheet that Is the target of the code below.
I need to print specific columns from this worksheet - and I don't know how.
The worksheet columns I need to print out are columns A:B, D:I, O, V, and W.
I would like to also use the file-name as the header in this printed listing if that is possible.
Can an Expert help me out, please?
Many thanks in advance.
' Open the Excel Spreadsheet

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("\\nfs1\dept\purchasing\_Reporting_Periodic\Annual_InventoryWorksheets\ZZ_COUNT_SHEET_NON_STAGING.xls")





'Insert columns required for physical inventory of the items on this worksheet

Dim variance_frmla

Dim valuation_frmla

Dim i 

Dim rg 



'Disable screenupdating so the code runs faster

objExcel.ScreenUpdating = False



'Insert column headers beginning in Cell V2 (V as in Victor)

objExcel.Cells(2, 22).Value = "Notes"

objExcel.Cells(2, 23).Value = "Count1"

objExcel.Cells(2, 24).Value = "Variance1 [Count Qty] - [Count1]"

objExcel.Cells(2, 25).Value = "Valuation1 [Variance1] X [Unit Cost]"

objExcel.Cells(2, 26).Value = "Count2"

objExcel.Cells(2, 27).Value = "Variance2 [Count Qty] - [Count2]"

objExcel.Cells(2, 28).Value = "Valuation2 [Variance2] X [Unit Cost]"

objExcel.Cells(2, 29).Value = "Count3"

objExcel.Cells(2, 30).Value = "Variance3 [Count Qty] - [Count3]"

objExcel.Cells(2, 31).Value = "Valuation3 [Variance3] X [Unit Cost]"



'Apply formatting across the column headers Z2 to AI2

With objExcel.ActiveSheet

	Set rg = .Range("V2:AE2")

    rg.Interior.ColorIndex = 30

    rg.Font.ColorIndex = 44

    rg.Font.Bold = True

    rg.Font.Size = 8

    rg.NumberFormat = "general"

End With



'Insert the Variance & Valuation formulas into adjacent cells beginning with AA3

With objExcel.ActiveSheet

    Set rg = .Range("W3")



'Insert the Variance & Valuation formulas into adjacent cells beginning with AA3

        variance_frmla = "=IF(W3<>0,$P3-W3,0)"

        valuation_frmla = "=IF(W3<>0,($P3-W3)*$U3,0)"



        rg.Offset(0, 1).Formula = variance_frmla

        rg.Offset(0, 1).Copy

        rg.Offset(0, 4).PasteSpecial -4123 ' - 4123 = xlPasteFormulas

        rg.Offset(0, 7).PasteSpecial -4123

        rg.Offset(0, 2).Formula = valuation_frmla

        rg.Offset(0, 2).Copy

        rg.Offset(0, 5).PasteSpecial -4123

        rg.Offset(0, 8).PasteSpecial -4123

End With



'This copies the formula all the way down cols AA3, AD3, and AH3

'and then replaces the formula result with the value only (like doing Paste Special --> Values)

With objExcel.ActiveSheet

    Set rg = .Range("W3")

    Set rg = .Range(rg, .Cells(.Cells(.Rows.Count, 1).End(-4162).Row, rg.Column)) 'xLUp = -4162

'    Debug.Print rg.Address



    With rg.Offset(0, -1).Resize(, 10)

        .FillDown

'>>> OMIT    .Formula = .Value

    End With

End With

 

'Must turn screenupdating back on

objExcel.ScreenUpdating = True





' Retrieve the separate Date components. 

Dim TodayYYYY, TodayMM, TodayDD

TodayYYYY = Year(Date) 

TodayMM   = Month(Date) 

TodayDD   = Day(Date) 



' Retrieve the IBU value from cell A3 for use in the file-name

Dim IBU

IBU = objExcel.Cells(3, 1).Value



' Save the sheet - appending TodaysDate to the end of the file-name.

objWorkbook.SaveAs "\\nfs1\dept\purchasing\_Reporting_Periodic\Annual_InventoryWorksheets\ZZ_COUNT_SHEET_NON_STAGING_" & IBU & "_" & TodayYYYY & "-" & TodayMM & "-" & TodayDD & ".xls"

Open in new window

0
Question by:OGSan
    12 Comments
     
    LVL 10

    Expert Comment

    by:SANTABABY
    Please review the uggested sample code. It does invoke the printing. Modify as required.
    Sub PrintSelected()
    
    '
    
    ' Macro1 Macro
    
    ' Macro recorded 9/7/2010 by santanub
    
    '
    
    
    
    
    
        Columns("C:C").Select
    
        Selection.EntireColumn.Hidden = True
    
        Columns("J:N").Select
    
        Selection.EntireColumn.Hidden = True
    
        Columns("P:U").Select
    
        Selection.EntireColumn.Hidden = True
    
        Columns("X:ZZ").Select
    
        Selection.EntireColumn.Hidden = True
    
        ActiveSheet.PageSetup.PrintArea = ""
    
        ActiveSheet.PageSetup.CenterHeader = "&F"
    
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    
            :=True
    
        Columns("A:ZZ").Select
    
        Selection.EntireColumn.Hidden = False
    
        
    
    End Sub
    

    Open in new window

    0
     
    LVL 1

    Author Comment

    by:OGSan
    I tried your recorded macro code above for use in my script - but I get a compilation error ("Expected statement") on stmt line 18 on the From:=1 parameter.
    0
     
    LVL 10

    Expert Comment

    by:SANTABABY
    Not sure why ... the current code prints only the first page, you can modify the params to alter it.
    However just to make sure that the necessary columns are printed:
    a) go ahead and delete line 18, 19,20,21 and run the sub. After that your worksheet should display only those columns that you're interested to print.

    b)Then go ahead and print the data.

    c)If you want to see the others columns (that you did not want to print), please
    do either of the following:
    1. Select the whole worksheet, click the "format" menu, select "Column ", select "Unhide"
    2. Use the following macro
    Sub UnhideAllColumns()
        Columns("A:ZZ").Select
        Selection.EntireColumn.Hidden = False
    End Sub

    Pls see below for the the updated routines and modify as required.
    Sub HideSelectedColumns()
    
    
    
        Columns("C:C").Select
    
        Selection.EntireColumn.Hidden = True
    
        Columns("J:N").Select
    
        Selection.EntireColumn.Hidden = True
    
        Columns("P:U").Select
    
        Selection.EntireColumn.Hidden = True
    
        Columns("X:ZZ").Select
    
        Selection.EntireColumn.Hidden = True
    
        ActiveSheet.PageSetup.PrintArea = ""
    
        ActiveSheet.PageSetup.CenterHeader = "&F"
    
        
    
    End Sub
    
    
    
    
    
    
    
    Sub UnhideAllColumns()
    
        Columns("A:ZZ").Select
    
        Selection.EntireColumn.Hidden = False
    
    End Sub
    

    Open in new window

    0
     
    LVL 1

    Author Comment

    by:OGSan
    Thanks for the reply, Santababy.
    The problem is that I am not working inside an Excel worksheet.  This code exists in a separate VB script file - which can be double-clicked to execute.  For example, if you copy the code that I pasted in the original thread, and save it as "VBSample.vbs" and change the path to something relevant, of course, it will execute when double-clicked.
    I'm looking for a solution that is written in VB script that I can add to existing code without having to modify it for use in a script file.
    I hope I'm making sense.
    0
     
    LVL 10

    Expert Comment

    by:SANTABABY
    Alright, thanks for the clarification.

    I see that your application (Excel)  variable name is objExcel.

    Could you please prefix the variable name in lines 3....12 and 19,20 (in the above code) ?

    Example line 12:
    objExcel.ActiveSheet.PageSetup.CenterHeader = "&F"

    0
     
    LVL 1

    Author Comment

    by:OGSan
    Thanks, Santababy, it's getting closer.  I tweaked it a bit to eliminate the error on invalid column reference on ZZ and it runs to completion - but I need the listing to be printed out immediately to whatever the default printer is.
    ' Print only desired columns for physical inventory
    
    objExcel.Columns("C:C").Select
    
    objExcel.Selection.EntireColumn.Hidden = True
    
    objExcel.Columns("J:N").Select
    
    objExcel.Selection.EntireColumn.Hidden = True
    
    objExcel.Columns("P:U").Select
    
    objExcel.Selection.EntireColumn.Hidden = True
    
    objExcel.Columns("X:AE").Select
    
    objExcel.Selection.EntireColumn.Hidden = True
    
    objExcel.ActiveSheet.PageSetup.PrintArea = ""
    
    objExcel.ActiveSheet.PageSetup.CenterHeader = "&F"
    
    
    
    objExcel.Columns("A:AE").Select
    
    objExcel.Selection.EntireColumn.Hidden = False
    

    Open in new window

    0
     
    LVL 10

    Expert Comment

    by:SANTABABY
    Great !!
    Insert the following code at line 12 above (which is a blank line right now ). Change '1'  to the desired #copies.

     objExcel.ActiveSheet.PrintOut Copies:=1
    0
     
    LVL 1

    Author Comment

    by:OGSan
    ...getting closer still:  I had to alter the statement to read,
       objExcel.ActiveSheet.Printout

    But I need to set the printout to landscape (it's defauling to portrait) and to have it fit onto the page.  Also - per my original request - I need to autofit the columns and to have column V appear after column W.  Ideally, column V should be 2" in width as well.
    0
     
    LVL 10

    Accepted Solution

    by:
    use the following code.

    Note:
    If you want to print Colmn V after Coumn W, I can't think of a  smarter way other than moving column V to the right of Column W temporarily.

    I listed the sample code (along with the 'undo' piece, i.e. move coulmn V back to its original place after printing).

    'print landscape :
    
            objExcel.ActiveSheet.PageSetup.Orientation = xlLandscape
    
    
    
    
    
    'Autofit columns 
    
        objExcel.Cells.Select
    
        objExcel.Selection.Columns.AutoFit
    
    
    
    
    
    
    
    'Set column V's width to 2 Inches
    
        objExcel.Columns("V:V").Select
    
        objExcel.Selection.ColumnWidth = 26.71 '=192 pixels = 2 inches
    
    
    
    
    
    'To temporarily move column V after W 
    
        objExcel.Columns("V:V").Select
    
        objExcel.Selection.Cut
    
        objExcel.Columns("X:X").Select
    
        objExcel.Selection.Insert Shift:=xlToRight
    
    
    
    
    
    'To move coulmn V back to its original place after printing
    
        objExcel.Columns("W:W").Select
    
        objExcel.Selection.Cut
    
        objExcel.Columns("V:V").Select
    
        objExcel.Selection.Insert Shift:=xlToRight
    

    Open in new window

    0
     
    LVL 1

    Author Comment

    by:OGSan
    Hi, Santababy -
    I'm getting an "Expected statement" on the Shift:=xlToRight on line 20 above.
    0
     
    LVL 1

    Author Comment

    by:OGSan
    I've tweaked the code and at least I'm getting it to execute now - But it is ignoring the hidden column formatting for some reason.  I'll close this question and post my code under a new question.
    0
     
    LVL 1

    Author Closing Comment

    by:OGSan
    Response started off badly when recorded macro code was used in reply to specific request for VB script.  Responder left me hanging at the end.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    680 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    31 Experts available now in Live!

    Get 1:1 Help Now