Copy a cell from a previous worksheet using a macro

Hi grateful for any help with the following:

I am trying to copy a cell from a previous worksheet to a current sheet
so that when the information in the previous sheet changes then the
relevant cell in the current sheet also chaneges. The following code
only copies the information when the macro is initially run and does
not alter if the copied cell information changes.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 31/01/2006 by McNaughton
'
' Keyboard Shortcut: Ctrl+n
'
Cells.Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=30
ActiveCell.Offset(32, 0).Range("A1:D54").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveCell.Offset(-31, 6).Range("A1:A85").Select
Selection.ClearContents
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = ActiveSheet.Previous.Range("F86")
End Sub

The problem area is the last line of code befor EndSub.

Any help would be gratefully received.

Thanks

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 31/01/2006 by McNaughton
'
' Keyboard Shortcut: Ctrl+n
'
Cells.Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveCell.Offset(32, 0).Range("A1:D54").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(-31, 6).Range("A1:A85").Select
Selection.ClearContents
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = ActiveSheet.Previous.Range("F86")
End Sub

Hi, what is this macro suposed to do??

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...

damorrison wrote:
> Sub Macro4()
> '
> ' Macro4 Macro
> ' Macro recorded 31/01/2006 by McNaughton
> '
> ' Keyboard Shortcut: Ctrl+n
> '
> Cells.Select
> Selection.Copy
> ActiveSheet.Next.Select
> ActiveSheet.Paste
> ActiveCell.Offset(32, 0).Range("A1:D54").Select
> Application.CutCopyMode = False
> Selection.ClearContents
> ActiveCell.Offset(-31, 6).Range("A1:A85").Select
> Selection.ClearContents
> ActiveCell.Offset(0, -2).Range("A1").Select
> Selection.ClearContents
> ActiveCell.FormulaR1C1 = ActiveSheet.Previous.Range("F86")
> End Sub
>
> Hi, what is this macro suposed to do??

Hi, I am using this macro to transfer the generic information contained
in my "Home Accounts" work book form the previous month to the current
month eg standing orders. The last line should copy the closing
balance from the previous month to the opening balance of the current.
Whilst it copies the original info ok it does not update the opening
balance should the closing balance change for any reason.

Hope this helps.

Glen

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

Rob,

Apologies. Your second lot of code has solved my problem. I was
having a grey (haired) moment and some of the code that I had copied
and pasted from your original was not sitting on the correct line. As
you can see from my initial reply to you the "F86" is not sitting on
the correct line and this is how I had pated it into my macro. Once I
sorted that out my problem was immediately resolved. Many many thanks
for your assistance.

How's NZ?

Glen

Hi Glen,

No need to apologise, pleased I could help.
NZ's good, summertime now :-)
Are you a Kiwi/expat too?

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

Rob,

No its just that I knew it was summer time out there and it is
currently about -7deg C here!

Anyway many thanks once again for your assistance it is really very
much appreciated.

All the best,

Glen.