Auto Add Column with New Data | VBA

[techwalker]

Hello!

I have a worksheet with teammate information. This sheet tracks daily performance followed by a 5 Day Average, 4 Week Average and an Direct/Indirect Percentage on job functions. Currently, I have built a formula to pull data from a separate tab (data from a report) that I paste in each day. This formula is copied across all employees for each day of the month. I un-hide yesterdays date, hide the rest of the month, then copy and paste that new data as values so it doesn't change when the next days data is pasted in. Doing so isn't an issue but I'm wondering if there is an easier way to do this?

What I would like to accomplish is this...to paste data in the "Performance Data" tab and have that automatically create a new column on the "Aug20" tab (in this case, it would create a new column for 20-Aug. It would also need to set the previous tab as "values" while transferring the formula over to the next column. Is this possible? or at least something similar I can modify?

Thank you for any/all help!

Spreadsheet.PNG

[Bernie Deitrick]

The easiest thing to do is to record a macro using RELATIVE referencing while you complete the task.

Capture.JPG

Then, if you select the same relative cell that you had started with when recording the macro BEFORE you run the recorded macro, the steps will be performed on the correct range.

For example, you have column Z with formulas, starting in row 2. Select Z2, then do your stuff. Then next time, select AA2 - the new column of formulas starts there - and run the macro.

To use RELATIVE referencing, you may need to customize your ribbon to show the "Developer" tab:

Capture 2.JPG

[techwalker]

Ah, understood! I had tested with Macro recording but did not use relative reference, so I abandoned that thought process. I greatly appreciate your insight! I'll dig into this later this afternoon.

Thank you