I'm trying to copy all rows in a workbook into a seperate workbook
based on a date column, and an entered date range. I want to have this
loop so that it will get all rows, but I keep getting errors, or dates
that don't fall withing the date range being copied. Here's what i
have so far, with the date range being on the DATA sheet in b8 for
beginning and b9 for ending, right now it's erroring out on
activesheet.paste, any ideas?

Sub PullDown()
Dim UsedRng As Range
Dim LastRow As Long
Dim E As Range
Dim mySheet As String
With Application
.CutCopyMode = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
mySheet = ActiveSheet.Name
Sheets("DATA").Activate
BegDate = [B8].Value
EndDate = [b9].Value
Workbooks("project request.xls").Activate
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Set UsedRng = Range("B2:B" & LastRow)
For Each E In UsedRng
If E.Value = BegDate Then
If E.Value <= EndDate Then Range(E,
ActiveCell.End(xlToRight)).select
Selection.Copy
Workbooks("support by project.xls").Activate
If ActiveSheet.Name < mySheet Then
Sheets(mySheet).Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Paste
ActiveSheet.Paste
End If
Next E
End Sub

Well, as a first step, I would suggest that you enable the DisplayAlerts
property temporarily to be able to see what prompts you get from Excel. That
might help you quite a bit.

What error message do you get by the way?

Cheers
/MP

"Craig" wrote:

I'm trying to copy all rows in a workbook into a seperate workbook
based on a date column, and an entered date range. I want to have this
loop so that it will get all rows, but I keep getting errors, or dates
that don't fall withing the date range being copied. Here's what i
have so far, with the date range being on the DATA sheet in b8 for
beginning and b9 for ending, right now it's erroring out on
activesheet.paste, any ideas?

Sub PullDown()
Dim UsedRng As Range
Dim LastRow As Long
Dim E As Range
Dim mySheet As String
With Application
.CutCopyMode = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
mySheet = ActiveSheet.Name
Sheets("DATA").Activate
BegDate = [B8].Value
EndDate = [b9].Value
Workbooks("project request.xls").Activate
LastRow = Cells(Rows.Count, "b").End(xlUp).Row
Set UsedRng = Range("B2:B" & LastRow)
For Each E In UsedRng
If E.Value = BegDate Then
If E.Value <= EndDate Then Range(E,
ActiveCell.End(xlToRight)).select
Selection.Copy
Workbooks("support by project.xls").Activate
If ActiveSheet.Name < mySheet Then
Sheets(mySheet).Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Paste
ActiveSheet.Paste
End If
Next E
End Sub

I don't get any prompts from excel, the error in vba is run time error
'1004' Method 'Range' of object '_Global' failed on the

If E.Value <= EndDate Then Range(E, ActiveCell.End(xlToRight)).Select

line

Okay, let me try to figure this out now:

1) The code snippet you posted earlier, is that supposed to go into the
"support by project.xls" workbook?

2) You also start out on a sheet in the "support by project.xls" workbook
(not necessarily on the DATA sheet though)?

When I single-step through the code I get pretty odd results; e.g., how do
you know which cell is the ActiveCell of the "project request.xls" workbook?
And which sheet? That could be anything, right? If you have only one sheet it
should perhaps be all right, but you still wouldn't know which cell happens
to be the active one.

Maybe you have assumed that by iterating with E will automatically set the
ActiveCell? Regardless, I suggest you use for example the following simple
change:

Range(E, E.End(xlToRight)).Select

Also, the Paste() method failed for me. Again, the code relies heavily on
implicit objects, i.e., instead of saying for example
Worksheet("abc").Cells(blahblah) you just say Cells(blahblah). That's pretty
dangerous. Be explitic about which objects you're operating on.

You've got the following line:

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Paste

- Cells returns all the cells on the active worksheet
- Rows gives you all the rows of the active worksheet
- ... And, well, there are 65536 of them, and your popping back up again,
and taking one step down. So far so good, I suppose, although it seems a
little bit unnecessary to me
- But what you get from Offset is a Range, and Ranges do not expose a Paste
method. I suppose you cactually meant ...Offset().Activate?

However, next time through the loop you try to access the next E. But in
order to do that you need to re-activate the source workbook again, otherwise
things will blow up in your face.

Anyhow, what we now end up with is something like this (and I eventually
reactivate the original workbook and -sheet, for good measure :-)

==================================

For Each E In UsedRng
If E.Value = BegDate Then
If E.Value <= EndDate Then

Range(E, E.End(xlToRight)).Select
Selection.Copy
Workbooks("support by project.xls").Activate

If ActiveSheet.Name < mySheet Then Sheets(mySheet).Activate

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
ActiveSheet.Paste

Workbooks("project request.xls").Activate
End If
End If
Next E

Workbooks("project request.xls").Activate
Sheets(mySheet).Activate

==================================

This was more or less the smallest incremental change I could do in order to
get your code working. Happy hacking! :-)

Cheers,
/MP


"Craig" wrote:

I don't get any prompts from excel, the error in vba is run time error
'1004' Method 'Range' of object '_Global' failed on the

If E.Value <= EndDate Then Range(E, ActiveCell.End(xlToRight)).Select

line

That worked, I'm still going through some of your suggestions to clean
things up a bit, but for now, it's working. Thanks

You're welcome

/M

"Craig" wrote:

That worked, I'm still going through some of your suggestions to clean
things up a bit, but for now, it's working. Thanks