Auto Data paste in new cell

[GaurangThakar]

Hi ,
I need a help , I've one sheet name "PDA" . I'm copying cell no . C6 to I6 then I paste that value in cell no C7to I7. Then again I'm Copying cell no C6 to I6 (when gets refresh and new data arrived) and then I'm paste data in cell no C8 TO I7. Then again I'm copying cell no C6 TO I6 and paste that value in cell no C9 to I9 and so on... , Is it possible to paste data automatically in new cell when cell no C6 to I6 get auto refreshed


Thank you.

 

[Yongle]

Insert code below in sheet PDA's code window (right click on sheet tab\ View Code \ paste into the window that opens)

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Range("C6:I6").Copy Destination:=Range("C" & Rows.Count).End(xlUp).Offset(1)
    Application.EnableEvents = True
End Sub

If the code does not trigger when autorefresh occurs ...
insert formula below in any empty cell in sheet PDA
=NOW()
The above formula is volatile and recalculates if any value in the sheet is amended and Worksheet_Calculate is triggered

 

[GaurangThakar]

hi yongle thanks for support,
auto copy Done regular but there is a problem when paste, there is no value return of cell no c6 to i6. Atually I put formula in cell no C6 to I6 . In cell no c6 there is ='MAIN OI BNF'!K2, then in cell no D6 =VLOOKUP(B2,'MAIN OI BNF'!AU6:AV36,2,0) and so on... , when auto paste the value of c6 to i6 in next line i.e c7 to i7 the formating change and shown ='MAIN OI BNF'!K3, and so on.. . so i need to paste special values in cell. I Think you under stand what I mean to say.

 

[Yongle]

Try this ...

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(, 7).Value = Range("C6:I6").Value
    Application.EnableEvents = True
End Sub

 

[GaurangThakar]

Now its working...
Thank you so much....

 

[GaurangThakar]

Hi Yongle ,
One more query, whenever page auto update same value print three times , I want to only ones time data paste when page auto refreshed .

 

[Yongle]

It sounds like worksheet_calculate is being triggered 3 times every time the value is updated
- we need to work out why that is happening

Is formula =NOW() in a cell in that sheet?
Are you able to tell me which 3 things are triggering worksheet_calculate?
Are there any other macros in the sheet module ?
- please post the code

 

[GaurangThakar]

=now() applied but same problem create, there is total 3 sheet in it.
code is
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(, 7).Value = Range("C6:I6").Value
Application.EnableEvents = True
End Sub

 

[Yongle]

Apologies if I confused you ...
I did not ask you to put NOW() in a cell
Please remove it

I asked you a question Is formula =NOW() in a cell in that sheet?
The answer was NO


Do any cells in the worksheet contain volatile functions (see list below) ?
=NOW()
=TODAY()
=RAND()
=RANDBETWEEN()
=OFFSET()
=INDIRECT()

 

[GaurangThakar]

NO.
Have you seen the s.s i have attached? when the value auto update its trigger three times .

thank you,