I have a large number of daily bank balances to analyze. I created a
graph to monitor the balances. I made a drop down box to change the
account. That works just fine.

I created 2 more drop downs: one for the start date, the other for the
end date. I want to be able to choose a range of dates easily without
having to change the source data for the graph each time.

The date drop downs return the row number of the date (raw data is on
another sheet). For instance, October 16th is the first day on my
"Historical Balances" sheet. I select October 16th as the start date
and October 17th as the end date (using the drop down boxes). The
cells that are linked to these boxes will have values of 1 and 2,
respectively.

I want a macro that will pull the data that corresponds to these
rows. I have a start, but it gets stuck on the FirstRow part. The
cells that have the row numbers are L6 and L7.

Sub PasteDatesToGraph()

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

With Worksheets("Historical Balances")
FirstRow = .Range("A" & Worksheets("Graphs").Range("L6").Value)
LastRow = .Range("A" & Worksheets("Graphs").Range("L7").Value)
Set RngToCopy = .Range(FirstRow, LastRow)
End With
With Worksheets("Graphs")
Set DestCell = Worksheets("Graphs").Range("N2")
End With
RngToCopy.Copy _
Destination:=DestCell

End Sub

Why start a new thread?



DoooWhat wrote:

I have a large number of daily bank balances to analyze. I created a
graph to monitor the balances. I made a drop down box to change the
account. That works just fine.

I created 2 more drop downs: one for the start date, the other for the
end date. I want to be able to choose a range of dates easily without
having to change the source data for the graph each time.

The date drop downs return the row number of the date (raw data is on
another sheet). For instance, October 16th is the first day on my
"Historical Balances" sheet. I select October 16th as the start date
and October 17th as the end date (using the drop down boxes). The
cells that are linked to these boxes will have values of 1 and 2,
respectively.

I want a macro that will pull the data that corresponds to these
rows. I have a start, but it gets stuck on the FirstRow part. The
cells that have the row numbers are L6 and L7.

Sub PasteDatesToGraph()

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

With Worksheets("Historical Balances")
FirstRow = .Range("A" & Worksheets("Graphs").Range("L6").Value)
LastRow = .Range("A" & Worksheets("Graphs").Range("L7").Value)
Set RngToCopy = .Range(FirstRow, LastRow)
End With
With Worksheets("Graphs")
Set DestCell = Worksheets("Graphs").Range("N2")
End With
RngToCopy.Copy _
Destination:=DestCell

End Sub

--

Dave Peterson

The OP replied in the original thread that it's working.

Dave Peterson wrote:

Why start a new thread?

DoooWhat wrote:

I have a large number of daily bank balances to analyze. I created a
graph to monitor the balances. I made a drop down box to change the
account. That works just fine.

I created 2 more drop downs: one for the start date, the other for the
end date. I want to be able to choose a range of dates easily without
having to change the source data for the graph each time.

The date drop downs return the row number of the date (raw data is on
another sheet). For instance, October 16th is the first day on my
"Historical Balances" sheet. I select October 16th as the start date
and October 17th as the end date (using the drop down boxes). The
cells that are linked to these boxes will have values of 1 and 2,
respectively.

I want a macro that will pull the data that corresponds to these
rows. I have a start, but it gets stuck on the FirstRow part. The
cells that have the row numbers are L6 and L7.

Sub PasteDatesToGraph()

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

With Worksheets("Historical Balances")
FirstRow = .Range("A" & Worksheets("Graphs").Range("L6").Value)
LastRow = .Range("A" & Worksheets("Graphs").Range("L7").Value)
Set RngToCopy = .Range(FirstRow, LastRow)
End With
With Worksheets("Graphs")
Set DestCell = Worksheets("Graphs").Range("N2")
End With
RngToCopy.Copy _
Destination:=DestCell

End Sub

--

Dave Peterson

--

Dave Peterson