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!;)
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,971
Messages
5,508,474
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top