Often when I'm working with a workbook, we have multiple (sometimes dozens) of different sheets in a file. It can be frustrating to unhide all those tabs manually to see the data. Below is a simple snippet that will quickly unhide all tabs in the workbook.
'Run this code to unhide all tabs in workbook
On Error Resume Next
t = ActiveWorkbook.Sheets.Count
For i = 1 To t
ActiveWorkbook.Sheets(i).Visible = True
It's worth mentioning the three types of Visible statuses available to us:
Hopefully this saves you some time - I know it will for my projects.
You've been there... Trying to do a VLOOKUP and it just doesn't want to work. I've been in the habbit of using =TEXT(TRIM(A1),0) whenever I do a VLOOKUP using employee numbers, because in so many of our reports, sometimes these IDs are numbers, sometimes they're text, and sometimes their both. Just making the TEXT and TRIM functions a habit has saved me some headache.
Today, I ran into the situation where this still didn't resolve the issue. For whatever reason, this new system we have has been designed to add trailing characters to IDs. They looks like spaces, but they aren't, and TRIM doesn't clean them up. They're actually the CHAR(160) symbol.
Anyway, this formula is likely overkill, but sometimes overkill is just enough. This formula combines several different text cleanup functions:
Often - normally when I'm doing something related to email or letter generation - I need an easy way to capture the person's first name to open my communication. Normally I'd use Text-to-Columns, but if the data is poorly structured and there's a mix of FIRST LAST with LAST, FIRST that isn't always an option. I put this formula together to recognize how the name is formatted, and grab the first name accordingly. Hopefully it saves you some time.
=PROPER(IFERROR(IFERROR(LEFT(RIGHT(A1,LEN(A1)-FIND(", ",A1)-1),FIND(" ",RIGHT(A1,LEN(A1)-FIND(", ",A1)-1))-1),RIGHT(A1,LEN(A1)-FIND(", ",A1)-1)),LEFT(A1,FIND(" ",A1&" ")-1)))