Using VB Code to Shift Data

**savio21** · 02-18-2011, 03:15 PM

Hi guys,

I'm trying to create a worksheet to track and forecast work done for upcoming week.

The sheet is set up to have several groups in which individual tasks below are summed up to produce a total. This is supposed to span weekly across 3 months. Therefore, for the following week when the user inputs a new beginning week. It deletes the first column and shifts the data over leaving only the final column to filled out by the user.

For ex:

Beginning Date: February 19, 2011

Project Name | Task Name | Week Feb 19 | Week Feb 26 | so on (these are just headers)
Project 1 | (blank) | Total of tasks for Feb 19| Total of tasks for Feb 26
(blank) | Task 1 | Hours for task
(blank) | Task 2 | Hours for task
Project 2| (blank) | So on

Therefore, for the next week it should delete the previous 1st week and shift all the data over by 1 column while maintaing the formulas and data.

Result Expected:

Beginning Date: February 26, 20111

Project Name | Task Name | Week Feb 26 | Week Mar 5
and so on...

If a spreadsheet is necessary I'll try to upload on. Also, a note that the headers are in a formula linked to the beginning date. If a clickable button is require I'm all game, however if it can just notice that the date has changed and automatically update that is good enough too.

Thanks in advance

**stnkynts** · 02-18-2011, 05:14 PM

Not 100% sure on what you want to do. If you just need to delete column C then the code would look like this:

Please Login or Register  to view this content.

If sounds like you may have more than that going on though. It really depends more on how you wrote the other formulas in your sheets as to whether it will hold the values properly or not.

**savio21** · 02-21-2011, 01:01 PM

Not quite what I want. I have attached a sample of what I'm trying to accomplish, maybe you might have a better way of doing it.

Sheet 1 is for the current week, Sheet 2 would be for the upcoming week but would be on the same sheet.

After week 1 is done the data in the first column become irrelevant. Therefore, it would be fine if the data got overwritten when the columns data shifted. Also, there should always be 4 weeks, so although the column is getting deleted there should always be the same number of columns to input data. Hope that clarifies things and thank you for your response.