Newbie trying to use Macros to delete/add columns

Copper Contributor

Hello all, 

 

I am new to Macros/VBA but have watched several tutorials. Unfortunately I still can't seem to solve my problem with columns. So, in a nutshell, I have a workbook with data and corresponding graphs on each tab. Each tab is pretty similar: I show a rolling 12 months of data, with a column for each month (see screenshot). Then EVERY MONTH, when I have new data, I have to delete the oldest column, add a new column, format the new column, make sure totals and %'s includes new column, and lastly incorporate it into the graph. This is incredibly monotonous to do for a many tabs and then repeat all of it each month. Is there a way I can write an Excel Macro to delete/add columns and perhaps another one to update the sheet once I add my new data?? Thank you so much, any help would be much appreciated

4 Replies

Hi @Moss Levenso

 

From above i have some doubts, clarify them, i definitely solve your problem.

 

1. As per your screenshot, you have sheet which always showing 12 months figures. right?

2. Above sheet has report from Apr 17 to Mar 18 (12 months). Next month will you delete Apr-17, and Add Apr-18 values. right?

 

If both of the above is right means, i can write macro. Please specify the cell reference (i.e) from which column and row value starts.

 

 

 

Thanks for your reply. The answer to both of your questions is Yes. But I think I actually figured it out on my own yesterday. I used the Macro recorder and performed the steps I wanted to create the macro. It certainly took a few attempts to get it right. The only question I have now is: do you know if the macro recorder will write macro for specific cell references or relative to where your cursor is?? Example? If I delete column N and add a column to the left of column C, will the macro do exactly that when it is run? Or does it depend on where my cursor is. Say I had a certain cell selected when I recorded the macro. Will the macro delete and add a column relative to where that cell is? Do I need to watch what cell is selected when I execute the macro?? Thank you so much, Moss

Hi

 

I always do it for specific cell reference only. Will not write relative to cursor.  So don't worry.

 

Before running macro, make sure that excel file and excel sheet.

 

Because, recording macro will blindly record active sheet. So it will run on any excel sheet.

 

By adding sheet name & file name inside macro, it won't run on any unnecessary excel file.

Excellent! Thank you for the heads up.

 

I wrote multiple macros, each to be used in its own tab. So, in each tab I created a button (shape) and I assigned the correct macro to that button. That way, I have to be in the active sheet to click the button, and then the macro will then apply to that sheet, which is what I want. I believe I'm all set now. Thanks!