2021年3月10日星期三

Copy and Pasting the Cell Values from one sheet to another Sheet

When i press the run button Target.Address = "$B$6" Then cell is update to trigger the below code.

I have been using below code which i run first time it copies the cell value from sht2 B4 and paste into sht3 B4.

2nd time runs the code it copies the sht2 Q4 and paste into sht3 C4.

3rd time runs the code it copies the sht2 B4 and paste into sht3 D4.

4th time runs the code it copies the sht2 B4 and paste into sht3 E4.

5th time runs the code it copies the sht2 B4 and paste into sht3 F4.

6th time runs the code it copies the sht2 B4 and paste into sht3 G4.

7th time runs the code it copies the sht2 Q4 and paste into sht3 H4.

8th time runs the code it copies the sht2 B4 and paste into sht3 I4. and it is working perfectly i have attached a workbook which can be download and might be better for understanding.

Private Sub Worksheet_Change(ByVal Target As Range)  If Target.Address = "$B$6" Then  Dim sht2 As Worksheet  Dim sht3 As Worksheet  Dim col As Long  Set sht2 = Sheets("Sheet2")  Set sht3 = Sheets("Sheet3")    col = sht3.Cells(4, sht3.Columns.Count).End(xlToLeft).Column + 1  If col = 3 Or 8 Then      sht2.Cells(4, 17).copy  Else      sht2.Cells(4, 2).copy  End If  sht3.Cells(4, col).PasteSpecial xlPasteValues    End If  End Sub  

and i want same thing for 2nd Table that is

1st time runs the code it will copy the sht2 F4 and paste into sht3 L4

2nd time runs the code it will copy the sht2 U4 and paste into sht3 M4

3rd time runs the code it will copy the sht2 F4 and paste into sht3 N4

4th time runs the code it will copy the sht2 F4 and paste into sht3 O4

5th time runs the code it will copy the sht2 F4 and paste into sht3 P4

6th time runs the code it will copy the sht2 F4 and paste into sht3 Q4

7th time runs the code it will copy the sht2 U4 and paste into sht3 R4

8th time runs the code it will copy the sht2 F4 and paste into sht3 S4

so i tried and make below piece of code and added it with above code, where code for 1st table was working fine but 2nd Table is not updating the values and no error occurs.

col2 = sht3.Cells(4, sht3.Columns.Count).End(xlToLeft).Column + 13     If col2 = 13 Or col2 = 18 Then         sht2.Cells(4, 21).copy      Else        sht2.Cells(4, 6).copy      End If      sht3.Cells(4, col2).PasteSpecial xlPasteValues  

Any help to solve the problem will be greatly appreciated.

https://drive.google.com/file/d/1JIrRMdoexFS2QyoaTX6_yDw31muEEFTf/view?usp=sharing

Sht3 picture enter image description here

https://stackoverflow.com/questions/66576278/copy-and-pasting-the-cell-values-from-one-sheet-to-another-sheet March 11, 2021 at 12:00PM

没有评论:

发表评论