Please help. I need a code to find the date and copy range and paste it somewhere.

[pedie]

Hi,

Once i enter date in range ("b2") in sheet2. I want a code to find the date in col b:b in sheet1, corresponding to date is table, if date is found i want the
code to select the date, select current region (which will be table), copy it and specialpaste it with all using source theme
in sheet2 range k12.
Thank you all for your help!

Thanks pedie!

 

[VoG]

Try this: right click the Sheet2 tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Address(False, False) = "B2" Then
    Application.EnableEvents = False
    Set Found = Sheets("Sheet1").Columns("B").Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        Found.CurrentRegion.Copy
        Me.Range("K12").PasteSpecial Paste:=xlPasteValues
    End If
    Application.EnableEvents = True
End If
End Sub

 

[Queezy]

This will copy region if region is in column C in sheet1.

Sub Region_Copy()
Application.EnableEvents = False
Sheets("Sheet2").Range("K12").Value = WorksheetFunction.VLookup(Sheets("Sheet2").Range("B2"), Sheets("Sheet1").Range("B:C"), 2, False)
Application.EnableEvents = True
End Sub

 

[pedie]

VoG!

That works well. thanks alot!

 

[pedie]

Hi VoG!, hi everyone!

Sorry to re-start this thread again.
the other day it was perfectly working but now it does not anymore.
Please check what could be preventing this from working.

Thanks, pedie
My current code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Address(False, False) = "J7" Then
    Application.EnableEvents = False
    Set Found = Sheets("Data").Columns("F").Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        Found.CurrentRegion.Copy
        Me.Range("J9").PasteSpecial Paste:=xlPasteValues
    End If
    Application.EnableEvents = True
 
End If
End Sub

 

[VoG]

Make sure that events are enabled. In the Visual Basic Editor press ALT + G to open the Immediate Window, then in that window type


Application.EnableEvents=True

and press Enter.

 

[pedie]

Vog, i did that, it is still not working.....

 

[pedie]

My apologies Vog!

You got it working again...!
Thanks for Much!

Thank for alt + G thing it really cool!...I wish to learn more of short cuts like that.

Thanks again
Ped

 

[VoG]

My mistake - it should be CTRL + G.

 

[pedie]

Thanks Vog, thats okay.

May I also just ask...why does it stops working at times???

As in, is this suppose to work all the time or not?