Find a value and copy column; paste to next available on another sheet

[jrake40]

I have three sheets Sheet1 , Sheet2, Sheet3. I would like to create a VBA to do the following:

1. sheet1 A1 is populated with a date.
2. I would like to take Sheet1 A1 and find it on Sheet2 in range B1:O1. Once found copy that column down to last value.
3. Paste the above copied column to sheet3 in the next available blank column.


Thanks in advance for any help.

 

[seguin85]

Practicing my VBA, hope this works:

Sub findandcopy()
Dim SourceDate As Date
Dim EColumn As Long
Dim Source As Worksheet, Dest As Worksheet, Info As Worksheet
Dim NewData As Range


Set Source = Sheets("Sheet1")
Set Info = Sheets("Sheet2")
Set Dest = Sheets("Sheet3")


SourceDate = Source.Range("A1")


Set NewData = Info.Range("B1:O1").Find(SourceDate)
Set NewData = Info.Range(NewData, NewData.End(xlDown))


EColumn = Dest.Cells(1, Dest.Columns.Count).End(xlToLeft).Column + 1


NewData.Copy Destination:=Dest.Cells(1, EColumn)


End Sub