autofill column as far as table is long

[Keyss]

Hi guys,

I have a table of data, I've added a new column in 1st position (column A), new value in cell A:2.

I want a code to run the value down the column to the end of the length of the table.

Relevant part of code:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 15.43
Range("A1").Select
ActiveCell.FormulaR1C1 = "Customer Code"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=[CONTRACT.xlsx]Export!R2C1"
Range("A2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A2:A288")
Range("A2:A288").Select

As you can see if my reports vary in the number of rows next time, it is still going to paste the value down to A288.

Can anyone advise what I can put in its place sop that no matter what number of rows are produced from a new report it will only paste to the length of the table (sorry if the tech talk isn't correct, I'm just starting)

Thank you

 

[Norie]

Try this.

Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A1").Value = "Customer Code"

With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=[CONTRACT.xlsx]Export!R2C1"
    .Value = .Value
    .EntireColumn.Width = 15.43
End With

 

[footoo]

Based on the code posted by the OP:

[A:A].Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
[A:A].ColumnWidth = 15.43
[A1] = "Customer Code"
[A2] = Workbooks("CONTRACT.xlsx").Sheets("Export").[B4].Value
Dim rw%: rw = Cells(Rows.Count, "B").End(xlUp).Row
[A2].Copy [A2].Resize(rw - 1)

This does not do the same thing as Norie's code.

 

[Norie]

footoo

Why are you putting the value of B4 from the Export sheet in column A?

The OP's code puts the formula "=[CONTRACT.xlsx]Export!R2C1" in A2 and copies down, in A1 notation that formula is "=[CONTRACT.xlsx]Export!$A$2".

 

[Keyss]

Hi Norie,

Thank you, i will try and reply afterwards.

Grateful.

 

[footoo]

footoo

Why are you putting the value of B4 from the Export sheet in column A?

The OP's code puts the formula "=[CONTRACT.xlsx]Export!R2C1" in A2 and copies down, in A1 notation that formula is "=[CONTRACT.xlsx]Export!$A$2".

The OP's code puts the formula in A2, then converts to value, then copies down.

You are right about [B4] - it should be [A2]

 

[Keyss]

Hi Norie,

Worked perfectly, thank you. Doing a "Learn VBA' course in September, look forward to doing myself after that but very grateful for your assistance now.

Thank you