Selection based on two variables - copy and paste to another sheet

[LPepito]

Hi everyone,

I've been trying to copy two columns from one sheet to another but the difficult bit for me is the variables. The contents of the two columns depends on the dates entered via a macro by the spreadsheet user. The user enters the dates and then I would like to copy the data that is held in the cells between those two dates of the neighboring columns. I have tried so many different ways, but I can't seem to get it to work, perhaps someone could suggest a way forward?

My current attempt is the following:

Sub CreateTable ()

Dim FirstDate As Date, LastDate As Date, NextDate As Date, Search1 As Date, Search2 As Date
Dim StartSelection As Range, EndSelection As Range

FirstDate = Sheets("sheet 1").Range("D14").Value
'This is the starting date where the user has entered the first date

LastDate = Sheets("sheet 2").Range("I4").Value
'This is the end date entered by the user

Sheets("sheet 3").Select
Range("H2").Select

'This is where the list of dates are based on the user's start and end selections with the corresponding columns containing the data I want to copy and paste into the fourth sheet

Search1 = Range("H:H").Find(What:=FirstDate, After:=Range("H1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:xlByRows, SearchDirection:=xlNext).Activate


Do
ActiveCell.Offset(1,0).Select
NextDate = NextDate+1

Loop Until NextDate = LastDate

Search2 = LastDate

Search1.Offset(, 2).Activate
Set StartSelection = ActiveCell

Search2.Offset(, 2).Active
Set EndSelection = ActiveCell

Range(Search1, Search2).Copy

Sheets("sheet 4").Select
Range("AA5").PasteSpecial

End Sub

I've spent so many hours trying to work this out and it's slowly wearing me down - any help would be amazing!

 

[kvsrinivasamurthy]

Is it possible to post sample data in XL2BB . and explain the required data from that .

 

[LPepito]

I'm afraid I haven't been able to get XL2BB but I can post some images if that helps?

 

[kvsrinivasamurthy]

Post the image.