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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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

ADVERTISEMENT

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

ADVERTISEMENT

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,643
Messages
5,660,040
Members
418,544
Latest member
Stefan Braem

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