Referencing previous worksheets in formula
 

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


--
cnaquin
------------------------------------------------------------------------
cnaquin's Profile: http://www.excelforum.com/member.php...o&userid=26242
View this thread: http://www.excelforum.com/showthread...hreadid=395345

Referencing previous worksheets in formula
 

Untested but give this a try...

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

--
HTH...

Jim Thomlinson


"cnaquin" wrote:


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


--
cnaquin
------------------------------------------------------------------------
cnaquin's Profile: http://www.excelforum.com/member.php...o&userid=26242
View this thread: http://www.excelforum.com/showthread...hreadid=395345

Referencing previous worksheets in formula
 

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

Thanks a bunch Jim

--
cnaqui
-----------------------------------------------------------------------
cnaquin's Profile: http://www.excelforum.com/member.php...fo&userid=2624
View this thread: http://www.excelforum.com/showthread.php?threadid=39534

Referencing previous worksheets in formula
 

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


--
cnaquin
------------------------------------------------------------------------
cnaquin's Profile: http://www.excelforum.com/member.php...o&userid=26242
View this thread: http://www.excelforum.com/showthread...hreadid=395345