How I used Excel to Win an X Box

I have read a few Excel books over the year, and go to http://www.mrexcel.com if I ever have something I’m trying to figure out or searching for something new to learn.  “Mr Excel” does daily podcasts and also has a few challenges every year.  In May, there was one where you had to use Excel’s newest add in, PowerPivot, alongside your own data set to create a dashboard.  The winning prize (there would be 2) was an XBox 360 with the Kinect.  I figured this would be a great prize for Bennie when she gets a little older.  After all, there is a “Melmo” game .

And in the meantime, I did hear Tiger Woods has Augusta this year, so who knows?  Take a look at the link to see the read up on my #winning (Charlie Sheen voice).  Pay close attention to the legend pictures on the SEC chart, I was very proud of that.

I’m always looking for Excel and Access consulting jobs, so if you’re interested, let me know.  I’m working on a testimonial page with examples to show the type of work I can do.

Enjoy!

http://www.mrexcel.com/Challenge2011/challenge_52011_win.html

Unhide all worksheets at same time

Excel 2007 made it easier to unhide sheets, but this macro unhides them all at the same time.  Very quick and easy to use.  I personally attach the user command ‘Ctrl+u’ to unhide all sheets because I use this macro almost daily.  It is also very functional to make files smaller by identifying hidden sheets that aren’t being used and deleting them.

Here’s the VBA code!

Sub Unhide_All_Worksheets()

Application.ScreenUpdating = False

On Error Resume Next

For Each w In Application.Worksheets

w.Visible = True

Next w

For Each z In Application.Charts

z.Visible = True

Next z

End Sub

 

Are you using Excel 2007 but like 2003 menus?

Here is some code (h/t to JWalk blog) on how to get the Excel 2003 menu, but show in Excel 2007.

Just enter this into any VBA module and run it, it will add to your add-ins toolbar.

Sub MakeOldMenus()

On Error Resume Next

‘ Delete it, if it exists

Application.CommandBars(“Old Menus”).Delete

On Error GoTo 0

‘ Create an old-style toolbar

‘ Set the last argument to False for a more compact menu

Set OldMenu = Application.CommandBars.Add(“Old Menus”, , True)

‘ Copy the controls from Excel’s “Built-in Menus” shortcut menu

With CommandBars(“Built-in Menus”)

.Controls(“&File”).Copy OldMenu

.Controls(“&Edit”).Copy OldMenu

.Controls(“&View”).Copy OldMenu

.Controls(“&Insert”).Copy OldMenu

.Controls(“F&ormat”).Copy OldMenu

.Controls(“&Tools”).Copy OldMenu

.Controls(“&Data”).Copy OldMenu

.Controls(“&Window”).Copy OldMenu

.Controls(“&Help”).Copy OldMenu

End With

‘ Make it visible. It appears in the Add-Ins tab

Application.CommandBars(“Old Menus”).Visible = True

End Sub

Are you a big fan of pivot tables?

I use pivot tables constantly for data mining.  When becoming comfortable with them, they become very powerful.  I’m not going to get into the detail of how to set them up or what they are used for, you are definitely my target market if you use them.  I’ve had Excel files before that have multiple pivot tables across many tabs.  This piece of VBA code gives you confidence that all pivot tables are refreshed, as this code will go through each pivot table on each tab and refresh accordingly.  This doesn’t take a lot of code, but is very easy to add to your personal workbook (see here).    Here you go…

Sub RefreshAllPivots()
‘Dim is creating the shortcuts for Worksheet and Pivot Table
Dim wks As Worksheet
Dim pt As PivotTable

For Each wks In Worksheets
‘Cycles through each worksheet

For Each pt In wks.PivotTables
‘Cycles through each pivot table

pt.RefreshTable
‘Refreshes the pivot table

Next pt
‘Skips to the next pivot table and refreshes until no more pivots to refresh on that worksheet
Next wks
‘Goes to the next worksheet and starts refreshing until all tabs have been seached and updated

End Sub

Hope this helps!

Using Access and Excel and formatting Excel data

I’ve worked with many companies that use Excel and Access together.  Copying tables or query results from Access and pasting into Excel can be beneficial to analyze if you are more comfortable in Excel.

When pasting data into Excel, the cell structure isn’t visually appealing.  The widths and heights are not setup that make it easy to read.  The following macro will put the data into an easy to read format.

Copy and paste this code (in between bolded rows) into your personal macro workbook (see earlier blog post).  You can set up keyboard shortcuts to any macro you create, I have this set up to Ctrl+j on my file.

START COPYING ON NEXT ROW

Sub Format_Access()

‘ Keyboard Shortcut: Ctrl+j

Application.ScreenUpdating = False

On Error GoTo Errormsg

If ActiveCell = Range(“a1”) Then

Columns(“A:A”).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.ColumnWidth = 106.71

Columns(“A:IV”).EntireColumn.AutoFit

Cells.Select

Cells.EntireRow.AutoFit

End If

If ActiveCell <> Range(“A1”) Then

ActiveCell.CurrentRegion.Select

Selection.ColumnWidth = 106.71

Selection.EntireColumn.AutoFit

Cells.Select

Cells.EntireRow.AutoFit

End If

Exit Sub

Errormsg:

MsgBox “There was an error in the macro, it did not run effectively!!”, vbCritical

Exit Sub

End Sub

STOP SECTION TO COPY ON PREVIOUS ROW

I use this macro on a daily basis, it comes in handy when your column structure isn’t visually how you want it.  The key is that cell selected will determine what area will be changed on the height and width.  Let me know if you have any questions.

Create an Excel file that holds just your personal macros…

I’m not taking any credit for this first post, but understanding how to build your own excel file that holds all of your personal macros will save MUCH time and effort.  I even have the file open automatically when excel opens, so my macros are always at my fingertips.

http://office.microsoft.com/en-us/excel/HA010872961033.aspx

Once you understand how this works, you can hop on board to some of the cool macros I’ve created over the years as a guy in Finance for various companies.