VBA Error "Cannot change part of a data table"

Question

I have code that creates pivot tables then populates another workbook with data. It opens a workbook that acts as a template for the data, populates it, saves and closes. It does this 8 times for 8 different sets of data. I've run this code many times before without issue and seemingly now it starts to error out with: "Cannot change part of a data table" on the second set of data (the first one works). The section that it is erroring out on is...

'Transfers Repairs Data
Range("D27").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[VBA Project.xlsm]Repairs Pivot'!R5C8:R60C11,4,FALSE)"
Selection.AutoFill Destination:=Range("D27:D30"), Type:=xlFillDefault
Range("D27:D30").Select
Range("D85").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[VBA Project.xlsm]Repairs Pivot'!R5C8:R60C11,4,FALSE)"

Any idea whats causing this?

Check `Range("D85")`, but check it in Excel, isn't it a part of 'data table' which could have been created in that range in the meantime? — Kazimierz Jawor, Apr 05 '13 at 13:50
To expand on @KazJaw's answer - there are certain "protected" areas in worksheets - things like data tables. You can't write directly into these areas. The error message suggests that's what you are attempting to do. Try running your code on a fresh sheet. Even better - make sure you reference the sheet (and not just the cell) when you call your code. By the way - you can (and ought to) rewrite your code without all these `.Select` statements... See [this link](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179) — Floris, Apr 05 '13 at 14:33

score 0 · Accepted Answer · answered Apr 05 '13 at 23:46

Not really an answer but an improvement to help you.

'Transfers Repairs Data
Range("D27:D30").FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[VBA Project.xlsm]Repairs Pivot'!R5C8:R60C11,4,FALSE)"
Range("D85").FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[VBA Project.xlsm]Repairs Pivot'!R5C8:R60C11,4,FALSE)"

Also be sure you're on the right sheet and as others commented that there's no data table where you're trying to paste these formulas. To use a particular sheet you can alter the above:

Sheets("Sheet1").Range("D27:D30").FormulaR1C1

VBA Error "Cannot change part of a data table"

1 Answers1