Simple Introduction To Using Variables In An Excel Macro

Excel macros

I love getting questions as they often give me ideas for new excel tutorials, etc. Recently, I received a question for a seemingly simple problem. Using a variable in such a case should make it easy to understand and will also give more flexibility. Why? Let’s first take a look at the question:

“You are doing wonderful job at posting easy excel tutorials. Continue the great work! Can you please post a macro tutorial that copy data from one tab and paste into another tab? For instance, the macro copies column A & B from sheet1 and paste into sheet 2, then copies column A & C from sheet1 and paste it into sheet3, and so forth?

Can you please help with this? I have attached my excel spreadsheet, which includes the data and simply macro – that is not working.”

Here is the screenshot of the file:

Many would have the reflex to simply record a macro that does this exact task. That would work fine. However, what if the next time there are more rows? Or you would like to change a few things? Using an excel macro with a variable is a great way to get it done.

Step #1-Define the problem: I want the macro to go column by column starting at column B until there is an empty column. For each column, I want the macro to create a sheet, copy both the first column and that column to the new sheet. Let’s first take a look at my code:

Name of the macro:

Add the variable “I” that will be the column number. And also select the sheet named “Detail” where the data needs to be.

Then, I add a loop that will make it possible for the macro to go from column to column:

Then, for each column I want to add a sheet, and rename it to the correct name:

Finally, I want to paste the information on each tab so I add a few more lines

You can also see the result after running the macro and of course download the spreadsheet here.


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss