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


***************************************************