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

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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!;)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: right click the Sheet2 tab, select View Code and paste in

Code:
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

New Member
Joined
Apr 23, 2007
Messages
28
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Vog, i did that, it is still not working.....:confused:
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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;)
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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?
 

Forum statistics

Threads
1,176,721
Messages
5,904,624
Members
435,102
Latest member
Randy N

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top