Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    a print macro (Excel)

    I have built a print macro, which has a selection of sheets. it goes throught tem and checks for certain things depending on the sheet and then prints.

    Attached it the code the excel file is too large to post even if zipped.

    I what to have a certian worksheet to have the ability to autofit columns Q to AF, then Hide any column if the cell in this range Q8:AF8 is blank " ".

    I have indicated my attempt in blue and the area where I think this code needs to be implemented is in red
    thank you
    Seaner

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: a print macro (Excel)

    I didn't spend any time going thru your code, since most did not seem related to your question. For your question this is all you need:

    <pre> Dim rCell As Range
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")
    wks.Columns("Q:AF").EntireColumn.AutoFit
    For Each rCell In wks.Range("Q8:AF8")
    If rCell = "" Then _
    rCell.EntireColumn.Hidden = True</pre>


    Steve

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: a print macro (Excel)

    or maybe this:
    Sheets("Chem Prop Schedule").Columns("Q:AF").Select
    Selection.Columns.AutoFit

    Sheets("Chem Prop Schedule").Columns("X:AF").Select

    Dim cell As Range
    For Each cell In Sheets("Chem Prop Schedule").Range("X8:AF8")
    If cell.Value = "" Then
    cell.EntireColumn.Hidden = True
    End If
    Next cell

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: a print macro (Excel)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>There is no need to select anything. Selecting just slows down the code.

    Steve

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: a print macro (Excel)

    goood point! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: a print macro (Excel)

    Thanks Steve, and Keith.

    I will use Steves;

    Steve I am not sure if the code I have there is totally effiecent, I used alot of web resources to build it. maybe you could take a look?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: a print macro (Excel)

    What is the code supposed to be doing?

    If you want us to look at your code, we need to know what it is supposed to do. I would say it efficiently does everything you have told it to do, but without knowing what you want the code to accomplish, I can not know how to make it more efficient.

    It would also be nice to have a sample file to run the code with (perhaps a before and after) so make sure any changes do what you want them to do...

    Steve

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: a print macro (Excel)

    ok, well basically the code prints worksheets in my workbook. about 8 of them.

    I first goes through each individual worksheet, and looks to see if the user made the autofilter set to non blanks so un needed rows are not shown in the print out.
    If the user did not use autofilter non blanks a error msg will pop up and the user will be exited out of the code and will have to goto the sheet and turn on the autofilter to non blanks

    then after doing the worksheet check, the code selects the worksheets specified and prints them using the active printer.

    the fill i am using it with is large so I cant attach it.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: a print macro (Excel)

    Just make an example file with the pertinent characteristics.

    Without that, you would force us to create an example file from scratch with no idea of how the data should be. Having us create an example file also adds a huge "activation energy" to the task and relegates to a task requiring more time to play, slowing down a response from us...


    Steve

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: a print macro (Excel)

    Yes I will make a example, thanks Steve!

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: a print macro (Excel)

    I had a chance to look a little at your code and some questions:

    Do you really want to quit if filters are not on the sheet or if they are not set to "non blanks"? If your intent is to set this and won't run unless it is, why not just have the code set it to filtermode and set the criteria?

    Your code is duplicated for each sheet you want to look at. Why don't you use a loop? You essentially create the array later when you select them.

    You should dim your variables and not select so much. Is this what you are after
    <pre>Option Explicit
    Sub PrintProgramEdit()
    ' - do Chem Prop Schedule cols formatting if not done
    ' - checks most sheets for Autofilter turned on and set to NonBlanks (Criteria1="<>"), warns then stops
    ' - selects Program sheets
    ' - prints Program sheets
    ' - unselects Program sheets
    '
    Dim vNames
    Dim x As Integer
    Dim rCell As Range

    vNames = Array("Cover Page", "Title Page", _
    "Procedure", "Chem Prop Schedule", "Calc Page", "Pricing")

    ' check AutoFilter for each sheet
    For x = LBound(vNames) + 1 To UBound(vNames)
    With Worksheets(vNames(x))
    <font color=blue> If .AutoFilter Is Nothing Then
    MsgBox "ERROR - Sheet '" & .Name & _
    "' AutoFilter not turned on for column A!" & vbCrLf & vbCrLf & _
    "Either turn it on (Select column A, then Data, Filter, Autofilter, " _
    & "then set it to '(nonBlanks)')" & vbCrLf & vbCritical
    Exit Sub
    ElseIf .AutoFilter.Filters(1).On = False Then
    MsgBox "ERROR - You need to set '" & .Name & _
    "' AutoFilter to '(nonBlanks)'!", vbCritical
    Exit Sub
    ElseIf .AutoFilter.Filters(1).Criteria1 <> "<>" Then
    MsgBox "ERROR - You need to set '" & .Name & _
    "' AutoFilter to '(nonBlanks)'!", vbCritical
    Exit Sub
    End If</font color=blue>
    End With
    Next

    With Worksheets("Chem Prop Schedule")
    .Columns("Q:AF").EntireColumn.AutoFit
    For Each rCell In .Range("Q8:AF8")
    If rCell = "" Then _
    rCell.EntireColumn.Hidden = True
    Next
    End With

    ' select all Program sheets

    On Error Resume Next ' turn on error handling
    ' print Program sheets
    Sheets(vNames).PrintOut Copies:=1, Collate:=True

    ' set ProgramLastPrinted cell to now
    If Err.Number = 0 Then Range("ProgramLastPrinted").Value = Now()
    End Sub</pre>


    If you just want to have the program turn on filtering and set column a for non-blanks, then just replace the blue lines above (the testing part) with this in red. It turns on the filter (if not on) and sets the criteria of column A to non-blanks:

    <pre> With Worksheets(vNames(x))
    <font color=red> If Not .AutoFilterMode Then
    .Range("A1").AutoFilter
    End If
    Range("A1").AutoFilter Field:=1, Criteria1:="<>"</font color=red>
    End With</pre>


    [I did not really test this, since I had workbook to test it on.]

    Steve

Posting Permissions

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