SOLVED

copy data to different worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2562830%22%20slang%3D%22en-US%22%3Ecopy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2562830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESir%20I%20have%20a%20two%20different%20excel%20file%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E1.copy.xls(Running%20in%20compatibility%26nbsp%3Bmode)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2.paste.xlsm%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ein%20copy.xls%26nbsp%3B%20sheet2%20having%20%22if%22%20formula%20in%20D2.%20When%26nbsp%3Bcondition%20match%20in%20D2%20get%20%22YES%22%20signal.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAnd%20this%20%22YES%22%20signal%20will%20keep%20changing%20and%20the%20name%20will%20also%20keep%20changing%20whenever%20condition%20match.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eit%20will%20copy%20C2%20name%20and%20paste%20it%20to%20%22paste.xlsm%22%20C3%20cell%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20it%20will%20autosave%20%22paste.xlsm%22%20after%20pasting%20the%20data.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2562830%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2562889%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2562889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1103126%22%20target%3D%22_blank%22%3E%40samnpti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20the%20values%20in%20column%20B%20on%20Sheet2%20in%20copy.xls%20change%3F%20Manually%2C%20or%20through%20VBA%2C%20or%20other%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2562920%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2562920%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esir%2C%3C%2FP%3E%3CP%3Eit%20is%20changing%20with%20reference%20from%20Realtime%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20find%20attached%26nbsp%3B%20.I%20have%20changes%20little%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2562928%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2562928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1103126%22%20target%3D%22_blank%22%3E%40samnpti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20dinner%20time%20where%20I%20live.%20I'll%20look%20at%20it%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563228%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563228%22%20slang%3D%22en-US%22%3Eohh!%20sorry%20..Please%20take%20ur%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563856%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1103126%22%20target%3D%22_blank%22%3E%40samnpti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20need%20to%20apologize!%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3Epaste.xlsm%20should%20remain%20open.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2564494%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2564494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESir%2C%3C%2FP%3E%3CP%3Etried%26nbsp%3B%20and%20working%20.%3C%2FP%3E%3CP%3Ebut%20when%20working%20in%20livefeed%20RTD%20(actual%20requirement)%3C%2FP%3E%3CP%3Eits%20showing%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22samnpti_0-1626756658007.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296985i727A30F4B552A494%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22samnpti_0-1626756658007.png%22%20alt%3D%22samnpti_0-1626756658007.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ebelow%20error%3C%2FP%3E%3CP%3E%22subscript%20out%20of%20range%22%3C%2FP%3E%3CP%3E(my%20changes%26nbsp%3B%26nbsp%3Bsignal%20from%20%26gt%3B5%20to%20%26gt%3B1)%3C%2FP%3E%3CP%3EBut%20both%20the%20file%20is%20opened.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2590606%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20data%20to%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2590606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1103126%22%20target%3D%22_blank%22%3E%40samnpti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20simply%20change%20%22C3%22%20to%20%22C15%22%20in%20the%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

@Hans Vogelaar

 

Sir I have a two different excel file

 

1.copy.xls(Running in compatibility mode)

2.paste.xlsm

 

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.

 

 

 

 

Thanks

 

17 Replies

@samnpti 

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

@Hans Vogelaar 

 

sir,

it is changing with reference from Realtime data

 

please find attached  .I have changes little

@samnpti 

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

ohh! sorry ..Please take ur time.

@samnpti 

No need to apologize!

See the attached version.

paste.xlsm should remain open.

@Hans Vogelaar 

 

Sir,

tried  and working .

but when working in livefeed RTD (actual requirement)

its showing 

samnpti_0-1626756658007.png

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

@samnpti 

Could you convert copy.xls to copy.xlsm?

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

@samnpti 

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

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

@Hans Vogelaar 

 

sir,

 

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)
Solution

@samnpti 

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
        ThisWorkbook.Save
        OldVal = Range("C3").Value
    End If
End Sub
Thanks its working
SIR

can you make change of range to C3 to C15?

@samnpti 

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
ThisWorkbook.Save
OldVal = Range("C3:C15").Value
End If
End Sub

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