Newb Help!

[ISBB]

OK i have 3 questions. I thought i had one of the 3 figured out but i guess not.

1st one: Is there a simpler way to clean up this code. What i am after is i have 3 worksheets and i need one collumn hidden and unhidden for prints. So say the first sheet to get printed is 90 & 91 Coll. I need collum AF unhidden for 1 print, and then hidden for 2 prints. same thing for the other 2 sheets. I know the copies only say 1 for the hidden section that is a no brainer :P

Sub Print_col_sheets()
'Print Collection Sheets
    Sheets("#90 & 91 Coll").Select
    Columns("AE:AG").Select
    Selection.EntireColumn.Hidden = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Columns("AF:AF").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("DSA COLL").Select
    Columns("Z:Z").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Columns("Y:AA").Select
    Selection.EntireColumn.Hidden = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("BCRF Coll").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Columns("AE:AG").Select
    Selection.EntireColumn.Hidden = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Home").Select
End Sub

Second One: Again just looking for a simpler way to print without having to watch the workbook select each sheet and print. These are just print the active shee which is fine if there is nothing to do with it.

Sub Print_Wk_totals()
'Print Weekly Totals
    Sheets("#90 & 91 Weekly").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("DSA WEEKLY").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("BCRF Weekly").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Home").Select
End Sub

Last but not least: I thought in the VB editor you could double click on say This Workbook and type in code you want automatically ran everytime this work book is open. IE.. everytime the workbook is opened i want it to go to the "Home" worksheet which is where i have all the nifty buttons i created for the other macro's and what not to use for navigation and printing sorting blah blah blah. I tried doing that but it didnt seem to work. Any ideas as to what im doing wrong?

 

[xenou]

1)
You can do with a lot less selecting. Also the recorder uses the window object a lot. Generally I write VBA with Worksheet and Workbook objects. The With/End With structure also simplifies the code for readability.

Sub Print_col_sheets()
'Print Collection Sheets
    With Sheets("#90 & 91 Coll")
        .Columns("AE:AG").EntireColumn.Hidden = False
        .Columns("AF:AF").EntireColumn.Hidden = True
        .PrintOut
    End With
    With Sheets("DSA COLL")
        .Columns("Z:Z").EntireColumn.Hidden = True
        .PrintOut
        .Columns("Y:AA").EntireColumn.Hidden = False
        .PrintOut
    End With
    With Sheets("BCRF Coll")
        .PrintOut Copies:=1, Collate:=True
        .Columns("AE:AG").EntireColumn.Hidden = False
        .PrintOut
    End With
    Sheets("Home").Select
End Sub

2) As above, using .Printout with the worksheet object but no need to select the sheet first.


3) You need to specify the workbook open event. Check out the dropdown list at the top of the code window in the ThisWorkbook object code pane...select Worksheet on the left, and the event desired on the right.

Private Sub Workbook_Open()
'Your code here blah blah blah
End Sub

[/code]

 

[xenou]

Note: Looks like no need for "entirecolumn" when you are already using an whole column range reference:
Columns(1).EntireColumn.Hidden = false
Columns(1).Hidden = false

Both work.

Also:
On the macro recorder you can very often remove everything from "Select" to ".Selection":

Range("A1").Select
Selection.Copy

Can be written as:

Range("A1").Copy

In a few cases, however, a sheet needs to be active to be worked on. So you might need to select (or rather, activate) the sheet:
Worksheets("Sheet1").Range("A1").Activate

 

[ISBB]

So if im reading alexanders code right it should looks something like this instead of what he put up or am i wrong on this

Sub Print_col_sheets() 
'Print Collection Sheets 
    With Sheets("#90 & 91 Coll") 
        .Columns("AF").EntireColumn.Hidden = False
        .PrintOut Copies:=1, Collate:=True
        .Columns("AF").EntireColumn.Hidden = True 
        .PrintOut Copies:=2, Collate:=True
    End With 
    With Sheets("DSA COLL") 
        .Columns("Z").EntireColumn.Hidden = True 
        .PrintOut Copies:=2, Collate:=True
        .Columns("Z").EntireColumn.Hidden = False 
        .PrintOut Copies:=1, Collate:=True 
    End With 
    With Sheets("BCRF Coll") 
        .Columns("AF").EntireColumn.Hidden = False 
        .PrintOut Copies:=1, Collate:=True
        .Columns("AF").EntireColumn.Hidden = True         
        .PrintOut Copies:=2, Collate:=True
    End With 
    Sheets("Home").Select 
End Sub

As far as the other print deal goes it would be just

Sub Print_Wk_totals() 
'Print Weekly Totals 
    With Sheets("#90 & 91 Weekly") 
         .PrintOut Copies:=1, Collate:=True 
    End With
    With Sheets("DSA WEEKLY") 
         .PrintOut Copies:=1, Collate:=True 
    End With
    With Sheets("BCRF Weekly") 
         .PrintOut Copies:=1, Collate:=True 
    End With
Sheets("Home").Select 
End Sub

or am i completely off base here?

 

[ISBB]

i must have dorked something up.. whenever i use my scroll wheel it seems like it deactivates the screen and then if i have vb editor open immediatley dumps me into there... everything looks right. I had this happen on one sheet and it was a range that got me but i cant figure this one out.

 

[ISBB]

Nevermind it seemed to have righted itself with a little close and re-open.. intresting..

Thanks for the help fellas i think im squared away for now. :D