Tuesday, July 7, 2020

Rename Excel Workbook Based on Number of Files Macro


Let’s say you have a task where you want to include a control number in an Excel sheet and in the name of the file which is based on the total number of Excel files contained within a folder. If you’re not familiar with writing VBA macros for Excel, see my getting started guide here.

For this example, there are currently three Excel files saved in a folder with these names:

Doc-Control-01
Doc-Control-02
Doc-Control-03

We want to save our latest document using the name Doc-Control-04, but how does Excel know to use “04” at the end?

As with any complicated task, the first thing I do is break it down into smaller, more manageable steps.

  1. The user selects the folder where to save the Excel documents
  2. The macro saves the open workbook into the folder with a “ZZZ” placeholder in the filename
  3. The macro counts the number of Excel files in the folder.
  4. The macro adds the control number based on this count into the spreadsheet
  5. The macro saves a copy of the workbook in the same folder but replaces the placeholder  “ZZZ” in the file name with the control number count
  6. The macro deletes the first copy the Excel document

Here’s a rundown of the VBA code:

Sub Generate_Documents()

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
 
Dim Wb2 As Workbook
Set Wb2 = ThisWorkbook
ThisWorkbook.Sheets(1).Select
Wb2.Activate

'---The control number will be in cell G7. For now we input placeholder “ZZZ”
Wb2.ActiveSheet.Range("G7").Value = “ZZZ”

'----save the Excel document VBA----
'dialog box to save the spreadsheet
Dim InitialName As String
Dim fileSaveName As Variant
InitialName = “Control Number: “ & Wb2.ActiveSheet.Range("G7").Value
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsx), *.xlsx")

If fileSaveName = False Then
        MsgBox "File not saved. You must save the file!"
        Exit Sub
       ‘exit the program so we don’t delete the template file
        Else
        Wb2.SaveAs Filename:=fileSaveName
        End If
       
‘now the macro knows what folder to look in to count the files

'Next, create the Control number by counting excel files in the folder and rename
'count existing excel files in the save folder
'First, get the location WB2 was just saved in
Dim WB2Path As String
WB2Path = Wb2.Path

Dim FileCount As Integer
Dim FolderPath2 As String, path2 As String

FolderPath2 = Wb2.Path

path2 = FolderPath2 & "\*.xls*"

    Filename = Dir(path2)
FileCount = 0
    Do While Filename <> ""
       FileCount = FileCount + 1
        Filename = Dir()
    Loop
    'MsgBox FileCount
   
     
    '---control number. If less than 10 add a 0 to display 01, 02, etc.
If FileCount < 10 Then
Wb2.ActiveSheet.Range("G7").Value = "0" & FileCount
Else
Wb2.ActiveSheet.Range("G7").Value =  FileCount
End If

Dim OldName As String, newName As String
OldName = Wb2.FullName

‘use the replace function to automatically replace placeholder “ZZZ” with the document control number
newName = Replace(OldName, "ZZZ", Right(Wb2.ActiveSheet.Range("G7").Value, 2))

‘save a copy of the sheet with the control number in the file name
Wb2.SaveAs Filename:=newname

‘delete the old workbook
Kill OldName

'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Let me know in the comments below if you have any questions how to rename a workbook based on the number of files in a folder.

Monday, June 22, 2020

Excel Macro to scroll to the top or bottom of the sheet


If you have a long sheet and are constantly scrolling all the way down to the bottom then back up to the top, here are two quick macros you can use to instantly go to the top or the bottom of the active sheet.

Sub GoTo_Top()
    With ActiveSheet
        Application.Goto Cells(1, 1), True
        End With
       
End Sub

Sub GoTo_Buttom()
    With ActiveSheet
        LastRow = Cells(Rows.Count, 2).End(xlUp).Row
        Application.Goto Range("B" & LastRow), True
    End With
End Sub

Use the developer tab to insert a command button, then link it to your macro to make it super easy for even the most inexperienced Excel user to instantly move from the top and the button of the spreadsheet.

If you don’t see the Developer tab at the top, go to File -> Options - > Customize Ribbon

I hope you enjoyed this quick tip! Sign up for my email list for more Excel tips.

Tuesday, May 12, 2020

Unique Excel Uses: Drum Machine

As someone who was a drummer in high school and college, this might be my favorite unique use of Excel yet. Dylan Tallchief has made an actual drum machine in an Excel spreadsheet. How cool is this?!
drum machine excel spreadsheet


Who knew we'd be able to make music in Excel? It really is a miracle program. Watch the video below to see the drum machine in action.


The part of the video where Dylan gets into the description of getting the VBA code to work is especially interesting to me.

sleepTime = 60 / Range("C23").Value * 1000 / 4   '2 will need to change to 4 when i add more resolution
swingL = sleepTime + ((sleepTime / 5) * Range("C26"))
swingS = sleepTime - ((sleepTime / 5) * Range("C26"))

Below is a video of myself messing around with the sheet and showing you how to change the beats per minute (BPM aka tempo) or how to add a swing groove to the sequence.


This is one template you'll definitely want to try and play around with yourself. Download the drum machine spreadsheet for yourself using the link in Dylan's video.

I'll have to add drum machine to my list of unique uses for Microsoft Excel spreadsheets. If you have your own unique use of Excel please do let me know!

Sunday, April 5, 2020

2020 NFL Draft Game in Excel

It's been a couple of weeks without sports now and man has it been hard. I've especially missed March Madness and filling out brackets.

I've been trying to think of ways to make up for the loss of sports. The 2020 NFL Draft is one of the few sports-related things still scheduled to go on (for now). My friend Isaic gave me the idea to make a NFL Draft Game in Excel.

nfl fantasy draft template in excel 2020


Inside this template I've listed the top 100 draft prospects according to ESPN. Each player (and the template is setup to handle ten players) are randomly assigned ten future NFL players by using a randomize macro. The earlier your players get drafter the better as the draft position counts for points and the lowest number of points wins!

Update 4/17/20 - new options added!

Based on some good user feedback I added the option where each player can now try to predict which team will select their players.

guess the nfl draft order game excel file


See how the Draft Game spreadsheet works in the video below:



Download the NFL Draft Game Excel Template here.

Even if you have no interest in the NFL, football, or drafts, you can still learn a bit about Excel by examining the random number generator macro, or the vlookup and sumif formulas used.

For now, the spreadsheet is setup to handle ten players. To add more, the formulas and macro will need to be modified. If enough people are interested in using this sheet, I will work on making it scalable so it can automatically adjust to the exact number of players.

Let me know if you like this game or if you have any suggestions or questions.

Sunday, January 19, 2020

Super Bowl Squares Template 2020 - SuperBowl Grid Game

The match-up for the Super Bowl is set which means it's time to download your Super Bowl Squares Template 2020 edition. Finally! We don't have to watch the Patriots. The Chiefs and the 49ers square off in Super Bowl 53 on Sunday, February 2nd at 6:30pm. To make the big game more interesting you can start a football office pool using our printable Super Bowl spreadsheet

Fill in the squares, watch the game, laugh at the commercials, and see who wins and maybe make a little money at the same time (or help raise money for charity). If you’re a fan of a team like mine (the Factory of Sadness Hope) that will never make it to the championship (or even get a taste of the playoffs), then playing Super Bowl Squares will make watching the game more exciting and gives you something to actually root for. Others may be torn between cheering for their favorite team and cheering for their squares.

2020 super bowl squares spreadsheet


This year’s Super Bowl Squares spreadsheet includes three different versions so you can choose the way you want to play:
  1. Printable version - This print ready version contains a blank grid, simply print it off and write all the player’s names down on the piece of paper. It’s quick and easy.
  2. Pick your own squares - Manage the office Super Bowl pool directly in Excel. In this version, each player gets to pick what squares they want. Once all are taken, press the randomize button to generate the random score numbers. No modifications are necessary, though you can if you so desire.
  3. Assign squares randomly - In this latest version, type each player's name in the manager sheet and use the drop down menu to assign how many squares to give each player. Then click the Assign Names Randomly button and it will automatically populate the grid with all of the names.

Super Bowl Squares Rules & How to Play


Listed below are the basic instructions on how to play Super Bowl Squares (which are also included within the spreadsheet for the three different versions, along with an example):

  1. Participants “purchase” individuals squares by writing their name in their desired squares, until all one hundred are filled.
  2. After all squares are taken, the numbers 0 through 9 are written in the empty horizontal and vertical rows in random order (draw the numbers out of a hat).
  3. After the end of each quarter of the game, match the last digit of each team's score to the corresponding square to find the winner.

Tip: 0, 3, and 7 are the best numbers to get. 8, not so much.

Added again this year is the option whether to keep the same random numbers chosen for all four quarters or to have four different sets of random numbers for each quarter. Using the rotating quarters method, someone could theoretically still get the number 3 for all four quarters, though that’s not very likely, or four different players could each get a 3 for different quarters.

Below is a short video tutorial I put together of how to use my Super Bowl Squares template in Excel.



Super Bowl Squares Template 2020 Download


The beauty of this football bowl manager is you will not have to change or modify any formulas yourself (unless you want to of course). Instructions are included within the Excel file and shows you exactly how to add more players (either manually or by using the button that is linked to a macro). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers. 

To be notified when any updates are made to the file or to get a notification when next year's template is available, download using this link(requires an email address):

Download the Super Bowl Squares Template 2020.xlsm file here

Please let me know in the comments or by email which version you'll use to play - printable, pick your squares, or randomly assign names. Or if you have any requests for modifications shout me out!

Monday, December 9, 2019

2019 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The college football conference championships were played this past weekend which means the 2019 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (other than giving and receiving gifts) is gathering around the TV and rooting for your Alma mater or hometown football team. This year has the added bonus of not just single bowl games but the sixthyear of a four team playoff to determine the national champion.

cfb bowl sheet


Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:
  • Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.
  • Updated leaderboard tab with new stats
  • Separate entry sheet to pass out to participants or co-workers that can be imported automatically by a built-in macro
  • Complete NCAA college football bowl schedule with game times and TV stations
  • New stat sheet to track each conference's record during bowl season (Go Big Ten! Go MAC!)
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2017 college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!
  • Download the CFP Pool Manager and Single Entry Form here.


How to Enter Player's Picks


There are now three ways to add participant’s data:
  • Manual entry using the drop down lists
  • Copy and paste from the selection sheet to the bowl manager
  • Use the import macro to automatically import a player’s data into the pool manager by way of a macro
Below is an older video of how to use the bowl pool manager spreadsheet.


The beauty of this football bowl manager is you will not have to change or modify any formulas yourself (unless you want to of course). Instructions are included within the Excel file and shows you exactly how to add more players (either manually or by using the button that is linked to a macro). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers.

I am working a version where you can add confidence picks by ranking the bowls in order of how confidence you are with your picks. I did one for 2017 but have not updated 2019's for this yet. Let me know in the comments or via email if this is something you would be interested in.

Here are some of my Bowl Game Observations that may interest only me...
  • Only bowl eligible team to not make a bowl game? My University of Toledo Rockets.
  • Most lopsided records? 6-6 Washington State vs 10-2 Air Force. Next you have 7-5 Washington vs 12-1 Boise State
  • There are now 6 independent teams not residing in a conference, 3 of which made bowl games. Can you name them?
  • Matchup with most combined wins: 13-0 OSU vs 13-0 Clemson
  • The ACC leads all conferences with 10 teams represented, followed by the Big Ten and SEC with 9 apiece.
  • There are no teams with losing records this year. There are 13 teams with 6-6 records, though there are no 6-6 vs 6-6 bowl match-ups
  • There are 22 teams with 10 wins or more

Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. I love that people are using this Bowl Prediction Game to help raise money for charity, that's so awesome to hear! What team are you rooting for?

Sunday, November 24, 2019

2019 Gift Guide for Excel Users

The 2019 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use.

So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories. Some of these items I already use on a daily basis and others are things that are on my own personal wish list. It's my biggest and best gift guide yet! Enjoy!

MY GO TO EXCEL BOOKS


Excel 2016 Bible - The complete guide to Excel 2016, from Mr. Spreadsheet himself! Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities by expert author and Excel Guru John Walkenbach to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more.



Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications. Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features.


If you’ve ever thought to yourself “there has to be a better way to do this,” while using Microsoft Excel, then know you're probably right. There probably is a better way to complete your tasks you just don't know what it is and you don't have time to read a boring, expensive, thousand page manual on how to use Excel. 76 Excel Tips to Increase Your Productivity and Efficiency is for you. No fluff, just Excel tips and tricks you can put to use right away.


OTHER BOOKS WORTH READING

Will It Fly? How to Test Your Next Business Idea So You Don’t Waste Your Time and Money by Pat Flynn. I’ve been following Pat’s blog and podcast for a number of years, and you might have seen some of his tips at work on my site. If you’re new to the online business world, this book is fantastic. Lots of practical steps to take to prove whether your idea has validity or not. Over 700 reviews and a five star rating, that's impressive!


The Martian by Andy Weir. If you only read one (fiction) book this year, The Martian has to be the one. I absolutely love this book (and it’s even better than the movie). As soon as I finished it the first time, I immediately re-read it – something I’ve never done before. It’s about an astronaut (with a great sense of humor) who gets left behind on a mission to Mars and has to figure out how to survive. If you’re interested in space exploration, problem solving, engineering, chemistry, botany, or disco + 70s TV shows, I highly recommend you read The Martian. Maybe the best book I’ve read in the past five years.




Another quick, shameless self-plug. Where are the most terrifying roller coasters found? Who designs them? Which park builds the craziest rides? Find out by reading my book 50 Groundbreaking Roller Coasters. Another reason for including this book on this list is to show you a real life usage of Excel. How's that? Because this is one of the books I wrote using an Excel spreadsheet!


Nowhere in the world is there a more bizarre theme park than Happy Fun Land. Nike Farmington’s twelve years of thrill-seeking and roller coaster riding has brought him to exotic locales like Perth, Australia, Kaatsheuvel, Netherlands, and Santa Claus, Indiana. He's marathoned a roller coaster for ten consecutive hours and conquered the world’s tallest and fastest. Yet nothing has prepared him for the insanity of Happy Fun Land and it’s mind blowing attractions: a drop ride with no brakes and a death simulator, just to name a few. Will Nike survive his hilarious adventure through the world's craziest theme park? I thought this book was hilarious and I think you will too!


Tools for the Job

Excel Quick Reference Sheets - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2016 (Windows Version). Written with Beezix's trademark focus on clarity, accuracy, and the user's perspective, this guide will be a valuable resource to improve your proficiency in using Microsoft Excel 2016. This guide is suitable as a training handout, or simply an easy to use reference guide, for any type of user.


TechSmith Snagit takes the hassle out of creating images and videos. Capture your screen, edit images, and deliver results. Snagit is also the only screen capture tool with built-in advanced image editing and screen recording. So you can easily create high-quality images and videos all in one program. Quickly explain a process, build visual-based documentation and be more engaging by adding images and videos to your communications. It's the tool I use to create all the images for Excel Spreadsheets Help and well worth the price.

Microsoft Surface Tablet. Need to use Excel on the go put don't want to lug around a larger laptop? A Surface tablet is great way to go.

Dimmable Eye-care LED Desk Lamp. A great lamp and exactly what I was looking for in a new clip on lamp for my drafting desk. It has six different light settings so I can find the right lighting for all of my needs. It is well built, works great besides being stylish.Besides using at work can use at home as a reading lamp too.

Keyboard Case for Tablets. How do I get so much done, especially when I’m traveling on the road a lot? I use a combination of a Samsung Galaxy Tablet and my new Keyboard case. They’re small so I can take it almost anywhere and the keyboard allows me to do things like type out this blog post, reply to your email questions, and write Excel macro code.



USB Heated Mouse / Hand Warmer. I'm not sure about you but the office at my day job can get really cold during the winter, especially after weekends or holidays. One solution I've found that helps is a heated mouse to keep you hand warm while not impacting my ability to get things done.

Laptop Privacy Screen Protector. Whenever I visit a customer I always take my privacy screen protector for my laptop. It keeps your personal or confidential information safe from prying eyes as you’ll see the information on your display while people on either side only see a darkened screen. If you’re ever on an airplane or in a coffee shop and feel like your neighbor is constantly looking over your shoulder at your screen then you need to get one of these today!



A good quality laser point. This laser pointer always comes in handy when it’s time for a meeting or presentation. Plus, it doubles as a toy to keep your cats entertained.



Rocketbook Smart Erasable, Reusable Wirebound Notebook with Penstation – the last notebook you’ll ever need!


Krieger Plug Adapters (Most of Europe (type C)) - If you're going to do some international travel I highly recommend taking this adapters with you. They come in a pack of four so you can share when your travel buddy forgets his.


Toys, Tech, Gadgets, and Others


Handheld Gimbal Stabilizer for Smartphone – Outside of this website, in my spare time I like to make and edit videos and montages (mostly of my family). It’s easiest just to use a cellphone but the video is often shaky due to my unsteady hands. So I decided to get a gimbal and I’ve had fun playing around with it.

Anker PowerCore Fusion - This is a portable power charger that plugs directly into the wall so it works as your regular charger, but is also a battery so you always have power on the go.


In my little free time from working and being a dad, I like to play around with my Samsung Gear VR headset. Virtual Reality is really taking off and the technology is getting much better. Though be warned it may cause motion sickness if the app you’re looking at doesn’t perfectly track your head movement.


Solar Powered Christmas Lights. I love putting up Christmas lights and trying to out-do my neighbors, but I have to admit I feel a little guilty about using the additional electricity. Luckily I found these solar powered Christmas lights and they actually work very well! They’re environmentally friendly and they automatically turn on and off each night. They also don’t have to be attached to a power source so I can put them in areas I normally couldn’t string lights.

Dash and Dot – programmable robots. If you’re into programming things like Excel macros and you want to teach your kids the joys of programming then I’ve found the perfect gift for you. These cute robots are designed to help introduce children to the wonderful world of programming. Oh, and they’re fun for adults too! Can’t wait to use this with my son in a few years. For now he just likes watching me drive it around with my phone.


Fitbit Alta. Let’s face it – us engineers nowadays sit in front of a computer a lot. I didn’t realize how much I wasn’t moving until I started wearing a Fitbit. It now helps me to stay motivated by tracking all-day activity like steps, distance, calories burned and active minutes so I can stay healthy for my family. When I’m sick I can’t answer your Excel questions and help you out, so I use Alta to help stay in shape and on top of my game!

Amazon Prime Membership. If you haven’t joined Amazon Prime yet, why not? I do almost all my shopping online and I get free two-day shipping on nearly everything. You can also borrow books, watch movies, and stream music. Get your Prime Discounted Monthly Offering here.

YOUR SUGGESTIONS?


Are you putting any of these items on your holiday wish list? If so, let me know which ones in the comments below. Do have anything you’d like to recommend to me?