OGSan
asked on
VBA to print non-contiguous columns in Excel worksheet
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.
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"
ASKER
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.
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.Hid den = False
End Sub
Pls see below for the the updated routines and modify as required.
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.Hid
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
ASKER
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.
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.
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.PageS etup.Cente rHeader = "&F"
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.PageS
ASKER
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
Great !!
Insert the following code at line 12 above (which is a blank line right now ). Change '1' to the desired #copies.
objExcel.ActiveSheet.Print Out Copies:=1
Insert the following code at line 12 above (which is a blank line right now ). Change '1' to the desired #copies.
objExcel.ActiveSheet.Print
ASKER
...getting closer still: I had to alter the statement to read,
objExcel.ActiveSheet.Print out
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.
objExcel.ActiveSheet.Print
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, Santababy -
I'm getting an "Expected statement" on the Shift:=xlToRight on line 20 above.
I'm getting an "Expected statement" on the Shift:=xlToRight on line 20 above.
ASKER
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.
ASKER
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.
Open in new window