Copy, Paste, Replace VBA Code

[thankyouvince]

Hello guys so I have this formula on say A2.

=IFERROR(IF(INDIRECT("'CAR 002'!G25")="x","Employer's Request",IF(INDIRECT("'CAR 002'!L25")="x","Contractor Request",IF(INDIRECT("'CAR 002'!R25")="x","Consultant Request"))),"")

How can i copy this formula on A3 but changing CAR 002 to CAR 003 and so fort using VBA macro. (CAR 004 on A4...)

Thank you so much guys!

 

[MrKowz]

Is there a need for VBA code? Put this in A2 and copy down.

=IFERROR(IF(INDIRECT("'CAR " & TEXT(ROWS($A$1:A2),"000") & "'!G25")="x","Employer's Request",IF(INDIRECT("'CAR " & TEXT(ROWS($A$1:A2),"000") & "'!L25")="x","Contractor Request",IF(INDIRECT("'CAR " & TEXT(ROWS($A$1:A2),"000") & "'!R25")="x","Consultant Request"))),"")

 

[thankyouvince]

Is there a need for VBA code? Put this in A2 and copy down.

=IFERROR(IF(INDIRECT("'CAR " & TEXT(ROWS($A$1:A2),"000") & "'!G25")="x","Employer's Request",IF(INDIRECT("'CAR " & TEXT(ROWS($A$1:A2),"000") & "'!L25")="x","Contractor Request",IF(INDIRECT("'CAR " & TEXT(ROWS($A$1:A2),"000") & "'!R25")="x","Consultant Request"))),"")

Thanks for the quick reply but YES i'm afraid I do need Macro because I have this button in Sheet1 that creates new sheet namely CAR 00x and the same time inserts new row below. Now this newly created sheet is linked in Sheet1 through the formula above.

Eg. i clicked button. (Sheet1 a1 value = 001)
- on sheet1 a2 value = 002 b3 = my formula
-sheet CAR 002 will be created
-and so on. a3=003 b3=formula above but CAR 003

Hope you get my point sorry for bad explanation.

 

[MrKowz]

When you click the button, is it safe to say that the sheet it creates is based on the last row in column A? For example, if A1:A10 are populated, the next sheet would be "CAR 011"?

If so, try:

Public Sub thankyouvince()
Dim oWS     As Worksheet
Dim nWS     As Worksheet
Dim LR      As Long

Application.ScreenUpdating = False
Set oWS = ActiveSheet
LR = oWS.Range("A" & Rows.Count).End(xlUp).Row + 1
Set ws = Sheets.Add
ws.Name = "CAR " & Format(LR, "000")
oWS.Range("A" & LR).Formula = "=IFERROR(IF('" & ws.Name & "'!G25=""x"",""Employer's Request"",IF('" & ws.Name & "'!L25=""x"",""Contractor Request"",IF('" & ws.Name & "'!R25=""x"",""Consultant Request""))),"""")"
oWS.Activate
Application.ScreenUpdating = True
End Sub