Add/delete columns based on the data of a cell

[Mizzness]

Hello.

Can you add/delete the amount of columns you have in a worksheet based on the data of a cell in another worksheet ?

Example:
Cell "A2" = 5 in "Summary" worksheet.
Amount of columns in "rental" worksheet is five.

By changing "A2" in the "summary" worksheet to 3 or 7, I would like the program to add the amount of columns I have in "rental" worksheet to seven or delete the columns to three (formulas incl.).

I'm new to VBA so I can rephrase if this doesn't make sense.

Thanks for any help.

 

[TonyJollans]

Hi Mizzness,

Yes, it can be done. You need to put code in the Worksheet_Change event procedure for your "Summary" worksheet which can pick up the change in values and act as it sees fit.

But ... when you say add columns do you want some data or formulae in the new columns and, if so, what is the source? The best way of going about it might depend on what you do with the "rental" worksheet after you have changed the count in the "summary" worksheet.


Enjoy,
Tony

 

[venkman]

Didn't like my previous answer?
thread707-542921
I take it you probably thought the previous answer was a little too complex, so I'm going to try to be more speciffic this time.
Here's some steps:

1. Open up the VBA editor in the workbook you are doing this all in.

2. double click on the worksheet that contains the cell with the number of columns on the left.

3. Add a handler for the worksheet_change function. To do this click the pulldown box that says "(General)" and select "Worksheet". Now click the pull down box to the right of that one and select "Change". You should now see the text:
[tt]
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub[/tt]

4. Inside this method (methods are subroutines and functions that are called by certain you will need to right code to check the address of target. This the address of whatever range has changed. For instance, assuming A2 is the cell that has that value, your first line in the method should be:
[tt]If Target.Address = "$A$2" Then[/tt]

5. Now get the value of that cell if it is in fact the cell that was changed:

[tt]iCols = Target.Value[/tt]

6. Here's the harder part where you're on your own, you must write a function which takes a number as an argument. It then will delete columns which you will hard code in your function or add columns, copying formulas. I suggest you use the macro recorder to see what this would look like.

This is a very do-able thing you are trying, but you will need to do some real coding to get it to work.

-Venkman

 

[venkman]

oops, typo, 4 shoud read:
4. Inside the method (methods are subroutines and functions that are called by an object) you will need to write code to check the address of target.

 

[Mizzness]

Venkman,

Your previous was fine but after doing some referenceing & research, I found that I still needed some help.
I'll try your recent response & see what happens.

Remember, I'm new to this & thanks again.

Mizzness