Autofill in Table

[G12Consult]

I have a table in excel and I am trying to fill down one of the columns to the last row in the table.

There is a formula in the first cell of the column and I need that to replicate down.

My code at the moment is:

ActiveSheet.Range("A7").Select
    Selection.AutoFill Destination:=Range("FeeEarnerTable[Employee No]")

On the line

Selection.AutoFill Destination:=Range("FeeEarnerTable[Employee No]")

I am getting the error

'Run-time error: 1004: Application defined or object defined error

 

[SkipVought]

Hi,

I'm a bit confused.

Seems you have a Structured Table named FeeEarnerTable.

If I'm correct, then entering a formula in the column headed, Employee No, will result in that formula being propagated to ALL rows in the table in that column.

???

BTW, are you ABSOLUTELY SURE that your table name is FeeEarnerTable and your field header is Employee No?

BTW2, this code is better: Select and Activate ought to be avoided as they slow down code execution...

Range("A2").AutoFill Destination:=Range("FeeEarnerTabl[Employee No]")

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[G12Consult]

Yes your correct and it does propagate all the rows.

However, I want to do the autofill because the formula in the first cell is '=IFERROR(IF(Fee_Earners!A2=0, "", Fee_Earners!A2),"")'

The information on the FeeEarners tab is dynamic (its pulling query results from SQL Server). so one time there may be 100 records and the next time there may be 500 records.

 

[G12Consult]

I forgot to mention, if there are more records in the table initially from another time then the returned results, a few REF errors appear.

If I manually copy down the formula it correct itself (hence just trying to automate this bit)

 

[SkipVought]

So my BTW and BTW2.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's what you can do to check your values.

In a cell OUTSIDE the table...
[tt]
1) enter =
2) select the Employee ID range
[/tt]
...result in my table
[tt]
=FeeEarnerTable[Employee No]
[/tt]

I'd guess yours will indicate something sightly different.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[G12Consult]

The result I get is:

=FeeEarnerTable[@[Employee No]]

I have another 10 columns in the table using this EmployeeNo column as vlookups and in their formulas I am using [@[Employee No]] and they all work

 

[SkipVought]

I duplicated your table and code.

Nothing wrong with your code if I and understanding your correctly.

Can you upload a laundered copy of your workbook?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[G12Consult]

any quick way of doing it!

 

[SkipVought]

Doing what?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[G12Consult]

masking all my data

 

[SkipVought]

I said launder.

Cut table rows/columns way down.

Delete unnecessary sheets.

Change sensitive data.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[G12Consult]

This work

ActiveSheet.Range("A7").Select
    Selection.AutoFill Destination:=ThisWorkbook.Sheets("Data").Range("A7:A1003")

I just read somewhere that apparently the source needs to be included in the destination.

Ideally I want to still use the table column name but not sure how to include that as source and destination

 

[SkipVought]

Hmmmmmm? No upload?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}