broro183 wrote:
Hi,
In answer to your question try using the line:
ActiveCell.Value = "='" & ActiveSheet.Previous.Name & "'!" & "F86"

Although I'm curious if it is worth copying this to a new sheet, with
the amount of info deleted, I guess all formatting will be copied
across too, so...
To shorten the amount of coding all "ActiveWindow.ScrollRow" can be
deleted & other lines can be modified as below:

Sub tester1()
Dim CopyFromSheet As String
CopyFromSheet = ActiveSheet.Name
Dim CopyToSheet As String
CopyToSheet = ActiveSheet.Next.Name
Sheets(CopyFromSheet).Cells.Copy Sheets(CopyToSheet).Range("a1")
Sheets(CopyToSheet).Range("A33:D86, G2:G86, E2").ClearContents
Sheets(CopyToSheet).Range("E2").Value = "='" & CopyFromSheet & "'!" &
"F86"
Sheets(CopyToSheet).Select 'if you want to see this sheet when the
macro finishes
End Sub

Please note I have attempted to identify the correct range of cells to
clear the contents of, if I have it wrong, just change the cells within
the apostrophes.

Hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=506711

Rob,

I tried your single line of code in place of my last line but the
result is still the same ie the macro does copy the initial value of
the cell "F86" to my opening balance line on my new month sheet but
this value does not change to reflect any alterations to the original
cell "F86." The only way I can get it to work is if I manually delete
the new month's opening balance and then copy the original cell "F86."
This is rather mandraulic and kind of defeats the purpose of setting up
this macro.

I also tried running your tester but the blasted machine does not like
the following line:
Sheets(CopyToSheet).Range("E2").Value = "='" & CopyFromSheet & "'!" &
"F86"
Apparently there is a compile error.

Any other thoughts?

Glen