I am very new to VBS, and have a question.
I am creating a report that will be upated with new data daily, that shows a rolling average of volume for the week.
The approach that I am taking is to manually enter 1 column worth of data into sheet 1: A1 to A16 contains category (will always remain the same), and B1 to B16 contains the manually entered volume for each category.
I would like a macro that would automatically copy the volume data and paste it into a new column with today's date (5/23), on a new sheet. This macro would be run daily, so that new columns would need to be created each day (with the updated date), to paste the manually entered data above. This is one of my problems as i am not sure how to write the macro so that it finds the next column to paste the data... i can only figure out how to overwrite existing data.
After the data is pasted, i would need a formula or line that calculates a rolling average. So on Sunday, i would have divide by 1, monday i would add Sun and Mon data and divide by 2... i cant find a way to have the average rolling, so i dont have to manually update the formula each day.
Any help will be greatly appreciated!