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
https://stackoverflow.com/questions/66576278/copy-and-pasting-the-cell-values-from-one-sheet-to-another-sheet March 11, 2021 at 12:00PM
没有评论:
发表评论