copy data to different worksheet


@Hans Vogelaar


Sir I have a two different excel file


1.copy.xls(Running in compatibility mode)



in copy.xls  sheet2 having "if" formula in D2. When condition match in D2 get "YES" signal.

And this "YES" signal will keep changing and the name will also keep changing whenever condition match.

it will copy C2 name and paste it to "paste.xlsm" C3 cell


and it will autosave "paste.xlsm" after pasting the data.







17 Replies


How do the values in column B on Sheet2 in copy.xls change? Manually, or through VBA, or other?

@Hans Vogelaar 



it is changing with reference from Realtime data


please find attached  .I have changes little


It's dinner time where I live. I'll look at it later.

ohh! sorry ..Please take ur time.


No need to apologize!

See the attached version.

paste.xlsm should remain open.

@Hans Vogelaar 



tried  and working .

but when working in livefeed RTD (actual requirement)

its showing 


below error

"subscript out of range"

(my changes  signal from >5 to >1)

But both the file is opened.




I Think due to earlier version of excel (Running compatibility mode) data is not getting transfer to other sorksheet.

It was happening during direct cell reference(="cell no" ) also to other worksheet not working


Could you convert copy.xls to copy.xlsm?

i could.But it will not work then.Only work in xls


I'm afraid I don't have a solution for your problem.

Hahaha!..Thanks for your support sir. Its really appreciable.

@Hans Vogelaar 




i have done some changes.Which may work .I have copy the cell from copy.xls and paste it as link in paste.xlsm in sheet 2


and make reference in C3 cell in sheet 1 from F2 of sheet 2


just help required from you to make it autosave.


whenever C3 value will be changed it will autosave the sheet.



best response confirmed by samnpti (Contributor)


Right-click the sheet tab of Sheet1.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Calculate()
    Static OldVal As Variant
    If Range("C3").Value <> OldVal Then
        OldVal = Range("C3").Value
    End If
End Sub
Thanks its working

can you make change of range to C3 to C15?


You can simply change "C3" to "C15" in the code.

I did in that way.But its giving error.



Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("C3:C15").Value <> OldVal Then
OldVal = Range("C3:C15").Value
End If
End Sub

Or can u make any changes in sheet 1 will save?