VBA to capture Formulae using data in separate w/books

Tj_El

Board Regular
Joined
Mar 28, 2003
Messages
69
Hi VBA Gurus.

I hope I have described the issue properly in the thread subject.

I have a macro that basically generates and saves a csv file from data in a workbook. I started by inserting a new worksheet into the workbook containing the raw data but what is actually required was that a new workbook be used instead of adding a worksheet.

Recorded macro for this but now the formulae below don't work.

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(OR(Sheet1!RC[5]=83,Sheet1!RC[5]=40),40,50)"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!RC[3]=3621015,3621010,3401040)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[9]"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[2]&""EX"""
Range("L2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-9]&"" - ""&Sheet1!RC[-11]"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-3]&""-""&Sheet1!RC[-6]"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-13]"
Range("N2").Select
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.AutoFill Destination:=Range("A2:N8440"), Type:=xlFillDefault
Columns("N:N").Select

I think the "Sheet1!RC" reference is the culprit but I don't know what to use to replace it so I need some expert help in fixing this.

Any helpers out there? Thanks in advance.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,070
Members
412,762
Latest member
sienweiw
Top