I am having trouble manipulating some code I have used for a similar
purpose in the past. I have 2 drop-down boxes on my "Graphs" sheet.
The first one specifies the start date to analyze, the second box
specifies the end date. There are 2 corresponding cells (L10 and L11)
that indicate the position of the selection in the list. I can figure
out the exact row number that I need to look at in my source sheet
("Historical Balances") from these cells.

I need help changing this code so that it will pull only the selected
range of dates and paste them back to my "Graphs" sheet. The first
date in the list (on the "Historical Balances" sheet) is in cell A2
(and it will remain there indefinitely). The last date in the list
will move as I add more data. Here is the code I have so far:

Sub PasteDatesToGraph()

Dim FirstRow As Long
Dim LastRow As Long
Dim DestCell As Range
Dim RngToCopy As Range

With Worksheets("Historical Balances")
FirstRow = .Cells( A & row specified in cell L10 on the Graphs
sheet
LastRow = .Cells( A & row specified in cell L11 on the Graphs
sheet
Set RngToCopy = .Range( A & FirstRow : A & LastRow)
End With
With Worksheets("Graphs")
Set DestCell = Worksheets("Graphs").Range("N2")
End With
RngToCopy.Copy _
Destination:=DestCell

End Sub

Any help would be much appreciated. Thanks so much.

Kevin

Maybe...

Sub PasteDatesToGraph()

Dim FirstCell As Range 'changed!
Dim LastCell As Range 'this, too!
Dim DestCell As Range
Dim RngToCopy As Range

With Worksheets("Historical Balances")
set Firstcell = .Range("A" & worksheets("graphs").range("L10").value)
set LastCell = .range("A" & worksheets("graphs").range("L11").value)
Set RngToCopy = .Range(firstcell, lastcell)
End With
With Worksheets("Graphs")
Set DestCell = Worksheets("Graphs").Range("N2")
End With
RngToCopy.Copy _
Destination:=DestCell

End Sub

(Your code looked easier to understand than the words <vbg.)



DoooWhat wrote:

I am having trouble manipulating some code I have used for a similar
purpose in the past. I have 2 drop-down boxes on my "Graphs" sheet.
The first one specifies the start date to analyze, the second box
specifies the end date. There are 2 corresponding cells (L10 and L11)
that indicate the position of the selection in the list. I can figure
out the exact row number that I need to look at in my source sheet
("Historical Balances") from these cells.

I need help changing this code so that it will pull only the selected
range of dates and paste them back to my "Graphs" sheet. The first
date in the list (on the "Historical Balances" sheet) is in cell A2
(and it will remain there indefinitely). The last date in the list
will move as I add more data. Here is the code I have so far:

Sub PasteDatesToGraph()

Dim FirstRow As Long
Dim LastRow As Long
Dim DestCell As Range
Dim RngToCopy As Range

With Worksheets("Historical Balances")
FirstRow = .Cells( A & row specified in cell L10 on the Graphs
sheet
LastRow = .Cells( A & row specified in cell L11 on the Graphs
sheet
Set RngToCopy = .Range( A & FirstRow : A & LastRow)
End With
With Worksheets("Graphs")
Set DestCell = Worksheets("Graphs").Range("N2")
End With
RngToCopy.Copy _
Destination:=DestCell

End Sub

Any help would be much appreciated. Thanks so much.

Kevin

--

Dave Peterson

Sorry Dave, I know I didn't do the best job of explaining my problem.
However, you seem to have a handle on what I'm trying to do. The
macro gets stuck on the FirstRow part when I try to run it.

What's in these cells?

worksheets("graphs").range("L10")
worksheets("graphs").range("L11")

Since you're using that to find the firstcell and lastcell, they had better
contain numbers--and so that A# becomes a valid address.



DoooWhat wrote:

Sorry Dave, I know I didn't do the best job of explaining my problem.
However, you seem to have a handle on what I'm trying to do. The
macro gets stuck on the FirstRow part when I try to run it.

--

Dave Peterson

There are formulas in these cells. The drop down boxes are linked to
cells L1 and L2 respectively.

Here are the formulas in cells L10 and L11:

L10: =L1+1
L11: =L2+1

I don't want to pick up the title row from the "Historical Balances"
page. That is the reason for the formulas.

The first date I have on the "Historical Balances" sheet is October
16, 2006. It is in cell A2. The drop down boxes are linked to
'Historical Balances'!A2:A5000.

You're not picking up the formula. You're picking up the values.

What are the values?

And if you changed the code, post back with your current code.

(It worked ok for me--with nice numbers in those two cells.)

DoooWhat wrote:

There are formulas in these cells. The drop down boxes are linked to
cells L1 and L2 respectively.

Here are the formulas in cells L10 and L11:

L10: =L1+1
L11: =L2+1

I don't want to pick up the title row from the "Historical Balances"
page. That is the reason for the formulas.

The first date I have on the "Historical Balances" sheet is October
16, 2006. It is in cell A2. The drop down boxes are linked to
'Historical Balances'!A2:A5000.

--

Dave Peterson

I finally got it to work. Sorry about the wild goose chase. I didn't
type "Set" before FirstCell and LastCell. That's what made it fail.

Thanks so much for your help. You rock.

And sorry about posting the new thread. I didn't realize that the
active older topics showed up on the right. I thought my new posts on
this one would get buried.

Thanks again.

Kevin