VBA: Insert New Columns in Table and Autofill

[roy5000x2]

Hello everyone.

So I just dived into VBA a few days ago in Excel 2013, and I'm trying to find a way to make the workbook used for filling out customer orders a bit more dynamic.
The vision is that when the sales rep is filling out the first page with the company info, they can enter in the number of locations the client has and then click a button to have the correct number of columns with the correct formulas in them appear in the various tables throughout the workbook.

I've been messing around with VBA to try and get it to work (the code I've been working on is in my work computer. I'll post it when I have access to it). I think I've come up with a way to add new columns into the tables. What I'm having trouble with is applying the formulas to the new columns. Given that the number of columns will by dynamic, the number of rows will also be dynamic (if inventory is added or deleted), and the formulas change from one row to another, I thought I would have one location column be in the table at all times so that I could just use to autofill across to the new columns. VBA doesn't seem to like it too much when I try something like:

Set oSource = Worksheets("Sheet1").Range("Table1[Location 1]") 
Set oFill = Worksheets("Sheet1").Range("Table1[Location 1]":"Table1[Location x]") 
oSource.AutoFill Destination:=oFill

I don't really have a way to name the different columns in the table either now that I think about it, so I've just been using whatever Excel likes to name "Location x" when it creates the new columns.

A quick recap of what I'm looking for:
User types in number of locations and clicks generate button
Excel adds in n-1 columns (due to the existence of Location 1)
Columns are named Location 2, Location 3, ... Location n (due to existence of Location 1)
Columns are autofilled from Location 1 so that the formulas and formatting are in place
Code must be flexible enough to allow new rows to be entered without needing to modify the code

Additional information
Excel 2013
There are many other columns surrounding the Location columns in the tables
There is data around the outside of the tables
The autofilled formulas ascend correctly across the rows, but do not autofill correctly when going down columns due to gaps for formatting purposes.


Thank you for your time and energy. Please let me know if you have any questions or need clarification on anything.

 

[roy5000x2]

Here's what I've been working with so far:

Sub InsertCol()
    Dim oInt As Integer
    Dim intColumnCount As Integer
    Dim oWS As Worksheet
    Set oWS = Sheets("Sheet3")
    intColumnCount = Range("A20")
    For oInt = 1 To intColumnCount
    oWS.ListObjects("Table1").ListColumns.Add (3)
    Next oInt
End Sub