Solved: Writing a formula in another sheet. Should work but I get #NAME?

[sassora]

I have some code that seems to do what I want but the formulas in the sheet are all #NAME?

The ideas is that they do : a1, a2, a3 ... and b1, b2, b3 ... etc

Sheets("Maintable_Data").Select
Dim Lastrow As Long
Dim Lastcolumn As Long

Lastrow = Cells(65356, 1).End(xlUp).Row 'Finds last row (in column A)
Lastcolumn = Cells(1, 256).End(xlToLeft).Column 'Finds last column (in row A)

Sheets("Maintable_Edit").Select
ClearColumns 'Just a macro that clears everything in the spreadsheet
Range("A1").Select
ActiveCell.FormulaR1C1 = "=Maintable_Data!A1"
Range("A1").Select
Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, Lastcolumn)), Type:=xlFillDefault
Range(Cells(1, 1), Cells(1, Lastcolumn)).Select
Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(Lastrow, Lastcolumn)), Type:=xlFillDefault
Range(Cells(1, 1), Cells(Lastrow, Lastcolumn)).Select


EDIT: Actually the formuls are are coming up as A1, which isn't too good

[rory]

You're passing an A1 style-reference to the FormulaR1C1 property which requires R1C1 style references. Change this line:
ActiveCell.FormulaR1C1 = "=Maintable_Data!A1"
to this:
ActiveCell.FormulaR1C1 = "=Maintable_Data!RC"

and see if that helps.

[sassora]

The RC seems to work, thanks. Now there's an autofill method error for this line:


Selection.AutoFill Destination:=Range(Cells(1, 1), Cells(1, Lastcolumn)), Type:=xlFillDefault


EDIT: But this was down to the data! Thanks :)

[rory]

Glad to help. FYI, you could just do this:
Dim Lastrow As Long
Dim Lastcolumn As Long
Sheets("Maintable_Data").Select

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row 'Finds last row (in column A)
Lastcolumn = Cells(1, 256).End(xlToLeft).Column 'Finds last column (in row A)

Sheets("Maintable_Edit").Select
ClearColumns 'Just a macro that clears everything in the spreadsheet
Range(Cells(1, 1), Cells(Lastrow, Lastcolumn)).FormulaR1C1 = "=Maintable_Data!RC"

[mdmackillop]

Just for consistency (and Excel 2007)

Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column 'Finds last column (in row 1)

[sassora]

Thanks, that will make it more robust.