VBA to automate excel

Hi Kailash,

As I mentioned in our earlier post, I don't want to mix Adding Sum with initial requirement, I have created a new VBA for it.
Note:
1) First change Start Date in Sheet1 @ B5
2) Then change Number Of Weeks Required in Sheet1 @ B3
3) Then add values to each week in Sheet2 from J7 to required Columns
4) Then Click Add Sum in Sheet2.

This is what you want?
PS: I added some column header for your references, so you know code doesn't disturb your other columns if you have any.
KailashKapal_InsertColumns_v4.xlsm

Hi Shums,

Thanks for providing the sheet.

There is a slight requirement change for  the Total column.

REquirement :

1) The value that can be entered in "Enter number of weeks" in Sheet1 should not be greater than 78
2) The total column is a fixed column after 78 columns starting from Week1 column
3) The total number of rows that can have a value in a particular column would be 40 For e.g. In Week1, we can have users to enter around 40 values till J46

I have attached the requirement sheet for your reference.

Let me know if any queries.
KailashKapal_Requirement.xlsm

Hi Kailash,

Please find attached with additional changes.
KailashKapal_InsertColumns_v5.xlsm

Hi Shums,

Thanks for providing the sheet.

But the below requirements are yet to be met :

2) The total column is a fixed column after 78 columns starting from Week1 column. Total Column would be the "CJ" column.
3) The total number of rows that can have a value in a particular column would be 40 For e.g. In Week1, we can have users to enter around 40 values till J46

Kailash,

Check properly,

You cannot have more than 78 number of weeks.
If you want to restrict user for entering more than 78 as you mentioned here

1) The value that can be entered in "Enter number of weeks" in Sheet1 should not be greater than 78

then you cannot have

2) The total column is a fixed column after 78 columns starting from Week1 column

The total number of rows that can have a value in a particular column would be 40 For e.g. In Week1, we can have users to enter around 40 values till J46

For now number of rows are opened you can enter as many rows in Sheet2 and you never mentioned you need to restrict number of rows as well.

Hi Shums,

I completely got your point.

Can we have the below in the sheet2 :

1) A fixed total column rather than having a button in Sheet2 which would add the values entered. Position of this Total column would be "CJ" in Sheet2

Oh Boy!

You have no idea, how much it takes for any experts to change the things!

In your earlier question too, I mentioned, please be specific what you need.

That Button is just to run macro for adding sum, anyhow you will need to run a macro once user fills those columns and rows.

If you want to position Total Column to Col CJ, what will be range of sum? from Week1 to specific Week? or from Week1 to Col CI?

RAnge of Sum would be from Week1 to Column CI

Hi Kailash,

Please find attached with requested change.
KailashKapal_InsertColumns_v6.xlsm

This is very near to my requirement.

Last thing to be incorporated in the excel sheet :

When we enter number of weeks as 4 in Sheet 1, it will insert 4 columns - Week1 till Week4 in Sheet 2. We will enter values for Week1 till Week4 for which the total would be populated in CJ on clicking on Add Sum button. Can we remove this Add Button and instead use a sum formula in CJ column ?

Also if now we enter number of weeks as 3 in Sheet 1, it will insert 3 columns - Week1 till Week3 in Sheet 2. It deletes all the previous values that were entered earlier. We should be able to retain the latest values  for Week1 till Week3 which were entered earlier and for which the total would be populated in CJ on clicking on Add Sum button.

Really appreciate your efforts and i am very thankful to you.

Hi Kailash,

For your another change request:

Can we remove this Add Button and instead use a sum formula in CJ column ?

I did mentioned in earlier post:

That Button is just to run macro for adding sum, anyhow you will need to run a macro once user fills those columns and rows.

When you are entering Week Numbers & Start Date in Sheet1, you are not entering any values for newly added columns and Macro would not know how many number of rows you gonna enter, so I cannot include adding Sum Formula in Column CJ. Once your data values updated, you just need to click that Sum Button, it will add Sum in Col CJ.

Your 2nd request of not deleting previous records been updated in attached.

If you are using attached sheet the first time as you do not have any weeks entered in Sheet2, Macro will insert new columns from Col J and  Sheet1's Start Date, If you already have some records earlier, then macro will insert new columns after the last occurrence of previous week and continuous week number plus continue adding 7 days to previous week's date.
If you already entered 78 weeks in Sheet2 or any number of week in Sheet1 Plus Previous Weeks record exceeds 78. Macro will prompt you to enter  left number of weeks.

I have also added a Delete Week Button, once you complete 78 weeks and want to start over, you can use the same sheet next year.

Hope this would be your last request!
KailashKapal_InsertColumns_Final.xlsm

Hi Shums,

The latest sheet provided by you is not as per requirement. In fact the earlier sheet was better and close to the requirement.

My requirement are as below :

Please find below the details of the requirement for your reference :

1) When user enters "Start Date" and then enters the number of weeks, then in Sheet2, equivalent number of columns listed as Week1, week2 etc along with start date of the week are inserted in "Sheet2" in column "J"
2) User then enters values in respective Weeks upon which the Total column "CJ" is updated
3) If the user changes the number of weeks in Sheet1, then the values entered earlier should be retained and new columns should be inserted e.g. If we enter 2 as number of weeks, then Week1 and Week2 columns would be created in Sheet2 at listed cell. If we had earlier entered value for Week1 as 5, then value in the Week1 cell should remain as 5
4) If we enter less number of weeks than previous entry, then the extra weeks should be hidden and total column should only have values from existing Weeks
5) Total number of weeks should not exceed 78
6) Total number of values to be entered for each week should not exceed 40

You have provided me the solution that is 70 % as per requirement. If you want, i will mark your solution as assisted solution and close it. But my requirement {mentioned above} is still not met 100 %.

Hi Kailash,

I have updated attached as per your earlier post

When we enter number of weeks as 4 in Sheet 1, it will insert 4 columns - Week1 till Week4 in Sheet 2. We will enter values for Week1 till Week4 for which the total would be populated in CJ on clicking on Add Sum button. Can we remove this Add Button and instead use a sum formula in CJ column ?

Also if now we enter number of weeks as 3 in Sheet 1, it will insert 3 columns - Week1 till Week3 in Sheet 2. It deletes all the previous values that were entered earlier. We should be able to retain the latest values  for Week1 till Week3 which were entered earlier and for which the total would be populated in CJ on clicking on Add Sum button.

Which you mentioned :

This is very near to my requirement.

Last thing to be incorporated in the excel sheet :

1) Add Sum Button Removed, Col CJ will have Sum formula for range CJ7:CJ47
2) Earlier Week would be retained, will not be deleted.

Hope above mentioned two things were the last things to be incorporated.
KailashKapal_InsertColumns_v6a.xlsm

Hello Shums,

Thanks for removing the Add Sum button.

My requirement was also to retain the value of the week inserted earlier { Week column alongwith the value inserted recently}  and not to insert duplicate column for the same week again.

Also if you can hide remaining weeks where we are not entering the values, it would be really helpful.

This is as per requirement. Thanks a lot !!

You're Welcome Kailash! Eventually Glad to help you :)