Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
Tag Archives: macro
Free Excel Webinar – recording Macros Tips Tricks and Traps
Getting started with macros
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
Expand to Fit to One Page in Excel
Macro solution
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
Excel VBA to Clear Manual Page Breaks
Be careful how you do it
When clearing page breaks in Excel VBA you need to be careful. There is one command that will clear page breaks but it will also affect other print settings.
Power Query Privacy Setting Problem
Excel 2016 solution
Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
Declaring Variables in Excel VBA
Using the Dim statement
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
Excel Cell Comments and Documentation
Macro to Create a Comment Report for a File
I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.
Excel VBA and Variables
Using Option Explicit
Variables can speed up your code and make maintenance a lot easier. You should always declare or Dim (technical term) your variables, here’s why.
VBA to Open CSV and Avoid Date Error
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
Japanese Yen Format in Excel
Applying it quickly
To apply the Japanese Yen format can take quite a few mouse clicks.
The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.
Sub JapaneseYen() Selection.NumberFormat = "[$¥-411]#,##0.00" End Sub
If you are unsure how to use macros, see the link below.
Getting Excel to Recognise an Email Address
Converting it into a hyperlink
Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.
Running an Excel Macro from a Graphic
Its easy to set up
These days running a macro off a control button seems to be old school and many people have started running macros off graphics.
Excel VBA Command to Clear the Clipboard
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
Excel Hiding and Unhiding Error Fix
Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.
Activating a sheet in VBA
Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.
Running a Macro From the Quick Access Toolbar
The Quick Access Toolbar (QAT) can really speed up your work in Excel. You can even attach macros to the QAT.
VBA macros that run other macros
When you become more advanced with macros and VBA programming (Visual Basic for Applications), you realise that you can create re-useable macros.
Unhide all sheets in Excel
In Excel if you need to hide data or workings sheets you can hide multiple sheets in one action. Unfortunately Excel won’t let you unhide multiple sheets in one step (even in the latest version). To get around this limitation you can use a macro that unhides all the sheets.
Restricting the Scroll Area in Excel
Non-VBA and VBA solution
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
Excel and the VBA Editor
Make the VBA editor screen your friend
When you first start out using VBA and writing VBA (macro) code it is useful that the VBA Editor helps you quickly identify when you have made a mistake. If a line of code has an error it will display a dialog box that explains the error and turns the line of code red.