Copy

[Syed Haider Ali]

Dear Friends,

I have a table with three col . Col A contains Date values Col B and
have other numeric data. I would like to copy from specific date rang
and then paste into sheet2 using userForm having combobox1 "From Date
and Combobox2 "To Date." and when we click the OK botton the specifi
data will copy into sheet2.

Please Code for the above.

Thanks and Regards,

Syed Haider Al

 

[Tom Ogilvy]

Assume the data is on Sheet1 with a header row on row1 and the first date in
A2. Further assume the dates are sorted ascending in column A. Assume your
OK commandbutton is named cbtnOK.

Private Sub cbtnOK_click()
Dim rng as Range, rng1 as Range
Dim rng2 as Range, res as Variant
Dim res1 as Variant
with worksheets("Sheet1")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))

res = Application.Match(clng(cdate(Combobox1.Text)), rng, 0)
res1 = Application.Match(clng(cdate(Combobox2.Text)), rng, 0)
set rng1 = rng(res)
set rng2 = rng(res1)
.Range(rng1,rng2).Resize(,3).Copy _
worksheets("Sheet2").Cells(rows.count,1).End(xlup)(2)
End With
End sub

 

[Tom Ogilvy]

That also assumed that each row would have a unique date.

another approach, perhaps simple would be

.Range(rng(Combobox1.ListIndex + 1), _
rng(Combobox2.ListIndex + 1)).Resize(,3).Copy _
Destination:=Worksheets("Sheet2") _
.Cells(rows.count,1).End(xlup)(2)


The original code can be adjusted to handle non unique dates if the dates
are ordered.

--
Regards,
Tom Ogilvy

Assume the data is on Sheet1 with a header row on row1 and the first date in
A2. Further assume the dates are sorted ascending in column A. Assume your
OK commandbutton is named cbtnOK.

Private Sub cbtnOK_click()
Dim rng as Range, rng1 as Range
Dim rng2 as Range, res as Variant
Dim res1 as Variant
with worksheets("Sheet1")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))

res = Application.Match(clng(cdate(Combobox1.Text)), rng, 0)
res1 = Application.Match(clng(cdate(Combobox2.Text)), rng, 0)
set rng1 = rng(res)
set rng2 = rng(res1)
.Range(rng1,rng2).Resize(,3).Copy _
worksheets("Sheet2").Cells(rows.count,1).End(xlup)(2)
End With
End sub

--
Regards,
Tom Ogilvy

"Syed Haider Ali"
message news:[email protected]...