Conditional Paste using the VBA

[luvbite38]

Hi,

I hope someone can help me out of here.

Using VBA

Can I copy a Range from Sheet called LookupLists (range = G2:G117). Where Cell G2 has a unique ID

And then go to Sheet called DATASHEET find the G2 value in row 1 of DATASHEET and paste (overwrite) the info as value in DATASHEET.

Kindest ever regards,

thanks thanks thanks for your time

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Paste_LookupLists()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range, rngCopy <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngCopy = Sheets("LookupLists").Range("G2:G117")<br>    <SPAN style="color:#00007F">Set</SPAN> Found = Sheets("DATASHEET").Range("1:1").Find( _<br>                                What:=rngCopy(1).Value, _<br>                                LookIn:=xlValues, _<br>                                LookAt:=xlWhole, _<br>                                MatchCase:=False)<br>                        <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Found.Resize(rngCopy.Count).Value = rngCopy.Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "No match found for " & rngCopy(1).Value, vbExclamation, "No Match"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[lozzablake]

try this

<Code>
Sub CopyRange()
Dim dblTarget As Double
Dim cFound As Range

'find the value to find
dblTarget = Sheets("LookupLists").Range("G2")

'find the value on sheet DATASHEET
Set cFound = Sheets("DATASHEET").Range("a1").EntireRow.Find(dblTarget)
If Not cFound Is Nothing Then
'copy over the data
Sheets("LookupLists").Range("G2:G117").Copy
Sheets("DATASHEET").Activate
cFound.PasteSpecial xlValues
Else
MsgBox "Data not found"
End If
End Sub
<Code>

 

[luvbite38]

You guys are super amazing..... May God bless you with a free Iphone 4s.......

THANKS THANKS THANKS A MILLION

 

[luvbite38]

hey guys,

if possible, can the above provided code be slightly tweaked?

everything is same but I need to find the values in column A now and paste the range transpose.

any suggestion will be much appreciated.

Thank in advance