I am having an issue on trying to use a macro to select and copy a
data from one sheet to another. I know of one way and that is to have
it search and find one entry at a time and then paste it in the next
sheet. This is very time consuming.

The following is another method that I am try but it seems to work on
only 10 rows; any more than that I will get an error message stating

Run-time error '1004'
Method 'Range' of object' _Global" Failed

Here is the code

Dim i As String
Dim j As String
Dim k 'As String
Dim l
Dim dStartDate As Date
Dim dEndDate As Date
dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/
yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/
yyyy"))
dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")
Range("A1").Select
Dim dataRange As Range

Do While ActiveCell.Value < ""
If ActiveCell.Value = dStartDate And ActiveCell.Value <= dEndDate +
1 Then
'If ActiveCell.Value = "dog" Then
i = ActiveCell.Row & ":" & ActiveCell.Row
End If

j = i
If j < "" Then
k = k & "," & j
If k = "," & j Then
k = j
End If
End If
j = ""
i = ""
ActiveCell.Offset(1, 0).Select
Loop
l = "" & k & ""
Range(l).Select

The code is simple, it just records each row that meets the date range
and then puts in the the range fromat. When I use the debug it will
show Range("1:1,2;2......etc)

Will someone please help me

I'd build the range differently:

Dim dStartDate As Date
Dim dEndDate As Date
Dim dataRange As Range

dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/yyyy"))

dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")

Range("A1").Select

set datarange = nothing
Do While ActiveCell.Value < ""
If ActiveCell.Value = dStartDate _
And ActiveCell.Value <= dEndDate + 1 Then
if datarange is nothing then
set datarange = activecell
else
set datarange = union(datarange, activecell)
end if
end if
ActiveCell.Offset(1, 0).Select
Loop

if datarange is nothing then
msgbox "No cells found!
else
datarange.entirerow.select
end if

===
Untested, uncompiled. Watch for typos.

wrote:

I am having an issue on trying to use a macro to select and copy a
data from one sheet to another. I know of one way and that is to have
it search and find one entry at a time and then paste it in the next
sheet. This is very time consuming.

The following is another method that I am try but it seems to work on
only 10 rows; any more than that I will get an error message stating

Run-time error '1004'
Method 'Range' of object' _Global" Failed

Here is the code

Dim i As String
Dim j As String
Dim k 'As String
Dim l
Dim dStartDate As Date
Dim dEndDate As Date
dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/
yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/
yyyy"))
dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")
Range("A1").Select
Dim dataRange As Range

Do While ActiveCell.Value < ""
If ActiveCell.Value = dStartDate And ActiveCell.Value <= dEndDate +
1 Then
'If ActiveCell.Value = "dog" Then
i = ActiveCell.Row & ":" & ActiveCell.Row
End If

j = i
If j < "" Then
k = k & "," & j
If k = "," & j Then
k = j
End If
End If
j = ""
i = ""
ActiveCell.Offset(1, 0).Select
Loop
l = "" & k & ""
Range(l).Select

The code is simple, it just records each row that meets the date range
and then puts in the the range fromat. When I use the debug it will
show Range("1:1,2;2......etc)

Will someone please help me

--

Dave Peterson

On Jan 4, 3:59*pm, Dave Peterson wrote:
I'd build the range differently:

Dim dStartDate As Date
Dim dEndDate As Date
Dim dataRange As Range

dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/yyyy"))

dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")

Range("A1").Select

set datarange = nothing
Do While ActiveCell.Value < ""
* If ActiveCell.Value = dStartDate _
* *And ActiveCell.Value <= dEndDate + 1 Then
* * *if datarange is nothing then
* * * * set datarange = activecell
* * *else
* * * * set datarange = union(datarange, activecell)
* * *end if
* end if
* ActiveCell.Offset(1, 0).Select
Loop

if datarange is nothing then
* msgbox "No cells found!
else
* datarange.entirerow.select
end if

===
Untested, uncompiled. *Watch for typos.





wrote:

I am having an issue on trying to use a macro to select and copy a
data from one sheet to another. *I know of one way and that is to have
it search and find one entry at a time and then paste it in the next
sheet. *This is very time consuming.

The following is another method that I am try but it seems to work on
only 10 rows; any more than that I will get an error message stating

Run-time error '1004'
Method 'Range' of object' _Global" Failed

Here is the code

Dim i As String
Dim j As String
Dim k 'As String
Dim l
Dim dStartDate As Date
Dim dEndDate As Date
dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/
yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/
yyyy"))
dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")
Range("A1").Select
Dim dataRange As Range

Do While ActiveCell.Value < ""
* If ActiveCell.Value = dStartDate And ActiveCell.Value <= dEndDate +
1 Then
* 'If ActiveCell.Value = "dog" Then
* i = ActiveCell.Row & ":" & ActiveCell.Row
* End If

* j = i
* If j < "" Then
* k = k & "," & j
* *If k = "," & j Then
* * k = j
* *End If
* End If
* j = ""
* i = ""
ActiveCell.Offset(1, 0).Select
Loop
l = "" & k & ""
Range(l).Select

The code is simple, it just records each row that meets the date range
and then puts in the the range fromat. *When I use the debug it will
show Range("1:1,2;2......etc)

Will someone please help me

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you for all of your help. It worked great