Newb Help!

ISBB

Board Regular
Joined
May 2, 2005
Messages
101
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

Code:
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.

Code:
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?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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.
Code:
Private Sub Workbook_Open()
'Your code here blah blah blah
End Sub
[/code]
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
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":
Code:
Range("A1").Select
Selection.Copy
Can be written as:
Code:
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

Board Regular
Joined
May 2, 2005
Messages
101
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

Code:
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

Code:
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

Board Regular
Joined
May 2, 2005
Messages
101
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

Board Regular
Joined
May 2, 2005
Messages
101
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,318
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top