issue with copy-paste

[DVM]

Dear Hans,

please refer to this file and as usual, Sheet MasterList and also, Loop_Data. A button Update Loop Data on MasterList (top right corner) will update / copy the values from MasterList to Loop_Data. However, in Col C in Loop_data, some thing strange is hasppening which is difficult to narrate but you could observe it.

Thanks and besr regards,

vilas Desai
file sent through email.

[HansV]

I''ll take a look later today - rather busy now.

[DVM]

Thank you and no problems. I know the problem could be that the formula instead of the actual value is copied in destination. But I would not know how this is resolved.

[DVM]

The other issue in MasterList is about this fomula in cellJ.
=VLOOKUP($B5,DV!H11:DV!I48,2)&$G5&"_"&H5
I have another formula in cell Y5
=IF(AND(J5>"",COUNTIF(J:J,J5)>1),CHAR(64+COUNTIF($J$1:$J5,$J5)),"")
I will land up in a circular error if I modify formula in J5 as below:
=VLOOKUP($B5,DV!H11:DV!I48,2)&$G5&"_"&H5&$Y5

How do i achieve =$J5&$Y5?

Please advise
Best regards
Vilas Desai

[HansV]

The formula in C5 on Loop_Data should be

=VLOOKUP($B5,DV!$H$11:$I$48,2,FALSE)&$G5&"_"&$A5

You can't have column J refer to column Y and column Y refer to column J - it's like a dog biting its own tail.

[HansV]

BTW, I haven't looked at the code since you have changed the VBA password. But I don't think it's necessary for me to view the code - you'll have to make it use the formula from my previous reply.

[DVM]

Thank you Hans.
I have not changed the code password here it is again vdesai!
The sheet Loop_Data is has no formulas written directly. All that it does is on click of the button,
first clear all the values from the wsh Loop_Data (A5 and upto end of rows) and then copy values from MasterList!
again into Loop_Data!
But I have rearranged the sequence of columns in Loop_data. Please see the code below

Code: Select all

Sub CopyData()
     Dim r As Long
     Dim m As Long
     Dim t As Long
     Application.ScreenUpdating = False
     Worksheets("Loop_Data").Range("5:50000").Clear
     t = 4
     m = Range("W" & Rows.Count).End(xlUp).Row
     For r = 5 To m
         If Range("I" & r).Value <> 0 Then
             t = t + 1
             'Loop Number
             Range("H" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("A" & t)
            'Service Area
            Range("B" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("B" & t)
            'Device Tag
             Range("J" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("C" & t)
            'Device
             Range("D" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("D" & t)
            'Measurement
             Range("F" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("E" & t)
            'Jn Box ID
            Range("K" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("F" & t)
            'Jn Box Term No.
             Range("L" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("G" & t)
            'Cable Tag
             Range("M" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("H" & t)
            'Marshalling Rack ID
             Range("N" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("I" & t)
            'Marshalling Rack Term No
             Range("O" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("J" & t)
            'PLC Panel TB no
             Range("R" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("K" & t)
            'PLC Panel term No
             Range("S" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("L" & t)
            'IO Tag
            Range("X" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("M" & t)
            'PLC Rack No
             Range("T" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("N" & t)
            'PLC Slot No
             Range("U" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("O" & t)
            'PLC Channel No
             Range("V" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("P" & t)
            'No of Wires
             Range("W" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("Q" & t)
         End If
     Next r
     Application.ScreenUpdating = True
End Sub

So, the problem is at col C in Loop_Data where the formula is being copied instead of the value. I may be wrong in this understanding, but what I want in Col C in Loop_Data is the value of Col J in MasterList.

[HansV]

Change

Code: Select all

             Range("J" & r).Copy _
                 Destination:=Worksheets("Loop_Data").Range("C" & t)

to

Code: Select all

            Worksheets("Loop_Data").Range("C" & t).Value = Range("J" & r).Value

[DVM]

That is perfect. Thanks a lot.