Button to add colums on a spreadsheet?

[cloudforgiven]

Hi I am new to vba programming 2013 and was hoping if someone can help me. I am trying to add 1 blank column to a spreadsheet based on the user input after they click this button which is an activex control. The column will end based on how many rows there are in my table, meaning if there are 10 rows I should not see a column on line 11 of the spreadsheet. Also here is what I have so far, please check below. My issue is that it generates the column that I want, but it does so for the whole column of that spreadheet and I only want it to generate a column until the last row of my table?
You can run the code in excel with a button if you want to see what it does, just to see what I am talking about.

Private Sub CommandButton2_Click()
Dim x As Variant


Dim ColumnNum
x = InputBox("Enter a column that you want to add: ", "What column?")
If x = "" Then Exit Sub
ColumnNum = x
ThisWorkbook.Sheets("Sheet1").Columns(ColumnNum).I nsert shift:=xlRight


ThisWorkbook.Sheets("Sheet1").Columns(ColumnNum).C learContents
End Sub

[pbaldy]

The trick I use in this situation is to record a macro in Excel doing the specified task, then analyze/adapt the code it creates.

[cloudforgiven]

Well the reason I want to write code instead of a macro is because the table changes rows/ columns so I will have to constantly make macros. If I write code this allows me more flexibility. But back to my original question, I know i need something like
ThisWorkbook.Sheets("Sheet1"). Copy Range ("A" & ColumnNum)

But when I do this the line is highlight with an error that say object does not support this property or method, an Iv tried looking online for the proper syntax but no luck? Any ideas?

[pbaldy]

Recording a macro in Excel creates VBA code. You can then adapt that code to your needs. Doing what you describe above creates:

Range("F4:F6").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

[cloudforgiven]

pbaldy How can I edit that code from the macro you posted so that it copies an entire column till the end of the my table automatically and makes a new column based on what input it gets from the user? instead of hard-coding the range size?

Since the table will constantly change if I manually put in the range, I will always have to change it so the column can be extended? I want to avoid this.

Also @ pbaldy thanks for the help so far.

[pbaldy]

You build it. This is from an Access app automating Excel, so a bit different:

xl.Range("G" & R & ":N" & R).Select

R is a variable holding a row number.

[cloudforgiven]

Instead of putting R can I use the variable I used to store the user's input? Which would be x(Does Not Store A NUMBER its a letter A-Z), since its the column THEY want? Also is xl variable declared as a Dim, cause the compiler doesn't know what xl is?

[ssanfu]

A couple of things:
You do know this is a forum for Access, not Excel... right? It sounds like you are strictly working in Excel. Just asking...

The column will end based on how many rows there are in my table, meaning if there are 10 rows I should not see a column on line 11 of the spreadsheet. Also here is what I have so far, please check below. My issue is that it generates the column that I want, but it does so for the whole column of that spreadheet and I only want it to generate a column until the last row of my table?

This is not the way a spreadsheet works. A column goes all the way to the bottom, which in Excel2010, is row 1,048,576. Access 2000/2003 the last row is 65,536.

You cannot end a column at row 10 - no way, no how, no time!

[pbaldy]

You can insert a partial column, sliding over a given set of rows and leaving the rest intact. I got the impression that's what was desired.