Referencing previous worksheets in formula

[cnaquin]

I am working on a macro to copy the last worksheet in a workbook at the
end of the existing worksheets and input formulas in the new worksheet
that reference the previous worksheet.

For example, consider the following code:

Dim ws As Worksheet

Set ws = Worksheets(Worksheets.Count)
ws.Copy After:=ws
Set ws = Worksheets(Worksheets.Count)
ws.Range("A1").Formula = “=’Sheet1’!A1 + 1”

How can I replace the ‘Sheet1’ name with the name of the previous
worksheet?

My attempt that resulted in a run-time error was:

ws.Range("A1").Formula =
“=Worksheets(Worksheets.Count-1).Range(“A1”) + 1”


Any suggestions are appreciated.

Thanks,

Carey

 

[Guest]

Untested but give this a try...

ws.Range("A1").Formula =
"=" & Worksheets(Worksheets.Count-1).Name & "!A1 + 1"

 

[cnaquin]

That's it!!!!!!!!!

Thanks a bunch Jim

 

[cnaquin]

With a bit of testing, it looks like this works if there is not a space
in the worksheet name. What should the code be if it is unknown that
the worksheet name has a space or not?

Thanks,

Carey Naquin