"this workbook"

Is it possible to get a macro to return to "this workbook" when storing data via a macro on an excel data base, instead of creating a macro with a specific workbook name in it i.e

Range("A7").Select
ActiveCell.FormulaR1C1 = "='[NHSFORM.xls]NHS FORM'!R2C8"

Thereby changing NHSFORM.xls in the macro - to "this workbook"

Range("A7").Select
ActiveCell.FormulaR1C1 = "='[This workbook.xls]This workbook'!R2C8"

As, once the form has been "saved as" eg RHH12345 the macro ceases to work as it is still looking for the NHSFORM.xls worksheet

Hope this makes sense.

Hello Tisnoti,

Drop the Workbook name in the formula. Excel assumes the workbook to be "ThisWorkbook" by default.

Sincerely,
Leith Ross

tisnoti,

can you use this:

Please Login or Register  to view this content.

is it this you are referring to?