Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Uma wants to create many worksheets in a new workbook that are named after values in range B2:B20. Episode 1072 shows a VBA macro that makes all of this possible.
posted by Bill at 9:00 AM
Terry from Springfield, MO asks about a quicker, one-click way to change the Move Selection After Enter direction. Episode 1071 show you how to solve this problem by recording a couple of quick macros.
posted by Bill at 9:00 AM
Cheryl asks how to format all new pivot tables that she creates. The macro recorder can handle this, if you change the name of the pivot table before recording the macro. Episode 1027 shows you how.
Labels: Macro, Pivot Table, VBA
posted by Bill at 9:00 AM
Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips.
posted by Bill at 9:00 AM
Laura asks how to display the Find dialog in a macro. The macro recorder won't do it. Application.Dialogs().Show won't do it. Episode 954 shows you a kludge to solve this problem.
posted by Bill at 9:00 AM
Rod sends in today's question. He has to export several tab-delimited text files for a third-party application. He has the data for each file on a separate worksheet in a workbook and would like a macro to save those worksheets as individual text files. In Episode 894, I record a simple macro, then edit it to solve Rod's problem.
Labels: Macro
posted by Bill at 9:00 AM
You've probably seen the AC/DC video running in an Excel spreadsheet. The author did some cool tricks to deliver the WAV file to your hard drive. In Episode 891, I'll take a look at the code to extract an object from a workbook.
posted by Bill at 9:00 AM
Can Excel announce a status when someone opens the workbook? This macro will let someone know if they are over or under quota. Episode 848 shows you how.
posted by Bill at 9:00 AM
Mark asks about how to replicate the AutoSum concept in a macro. The code in Episode 844 gets closer to the AutoSum functionality than the solution that I proposed in Episode 812.
posted by Bill at 9:00 AM
Mark from New Hampshire notes that the macro recorder can not record the simple act of pressing the AutoSum button. In Episode 823, I show you the arcane workaround to solve the problem.
posted by Bill at 9:00 AM
Ever wonder how MrExcel spends his free time? He watches the macro recorder write code before his eyes. In Episode 813, learn how to arrange the VBA window and the Excel window to watch which lines of code appear in response to actions in Excel.
posted by Bill at 9:00 AM
Rather than use a custom view, Episode 789 shows how to create a 3-line macro to unhide all worksheets.
posted by Bill at 9:00 AM
After recording a macro in yesterday's podcast, I take a look at the recorded code to look for instances where the macro recorder hard coded the number of records. With just a few tweaks, we have a working macro to automate the import and conversion of data from one system to another. Episode 774 shows you how.
Labels: Macro
posted by Bill at 9:00 AM
Pau asks how to take a CSV file from one website and convert it for importing to Google Calendar. In Episode 773, I turn on the macro recorder and attempt to fix the file.
posted by Bill at 9:00 AM
Continuing the information from podcasts 742 and 743, I record a simple macro today to open the Invoice.xlt file and then add a couple lines of code to update the invoice number and the date. Episode 744 shows you how.
Labels: Macro
posted by Bill at 9:00 AM
Dick sends in a cool question today. First, he figured an amazing way to grab the first value from a subtotaled group. But, Episode 712 shows three lines of VBA code to finish the process.
Labels: array formula, formula, Macro
posted by Bill at 9:00 AM
Back in Episode 695, I used the InputBox function in VBA to ask for a couple of answers. In today's podcast, I will show you how to convert that macro to use a custom user form in Excel. Episode 701 shows you how.
posted by Bill at 9:00 AM
In today's podcast, a five line macro that will allow you to scale a selected range. This macro was inspired by engineers in one of my recent VBA seminars. They had been using a similar tool for years and we were surprised it was a five line macro to replicate the tool. Episode 695 shows you how.
Labels: Macro, Scale Numbers, VBA
posted by Bill at 9:00 AM
You’ve created a simple macro and want to assign the macro to a button on a worksheet. In today’s question, someone is trying to assign a macro to a button, but it is not working. She can assign the macro to a text box and it works, but not with a button. Episode 594 shows how to assign a macro to a text box, an AutoShape, or a button on the Forms toolbar.
Labels: autoshapes, Macro, Textbox
posted by Bill at 9:00 AM
In offices throughout the US, people are starting to turn in their NCAA bracket sheets to the office commissioner. Whether you are asking accountants to fill out an NCAA bracket or sales reps to fill out a sales forecast, it makes sense to collect the data in an Excel workbook. In Episode 464, we take a look at a macro that can be used to gather entries from the individual worksheets and combine them into a master worksheet.
posted by Bill at 9:00 AM
Someone posed this question in a recent Power Excel seminar - he had to format cells with a strange format, and had to do this repeatedly. Learn how to record a simple macro to automate this task. Episode 384 shows you how.
posted by Bill at 9:00 AM
Back in August, the podcast showed a trick for preventing people from going outside a certain range of the worksheet. Today, a caller points out that this setting is lost when you close and re-open the workbook. Today's postcast shows how to create a simple one-line macro using Worksheets("Sheet1").ScrollArea = "A1:J10" to ensure that the setting always comes back. Episode 365 shows you how.
posted by Bill at 9:00 AM
