Establishing Rows to copy based on Offsets

[Tony Bender]

I want to create a macro that selects specific rows off a data table
and copies the rows to a second data table. The rows are specified
based on the user's selection off a drop-down list. The user selects
a brand name from the drop-down and with an Offset function it
identifies the first row number associated with the selected brand,
and that number appears in cell b41, and the last row number for that
brand appears in cell c41. I am having difficulty taking these two
numbers and creating a statement that will select these rows (and all
those in between)copy them to another data table. So for my example I
select brand X and the corresponding rows of data associated with this
brand are 7:21.

Here's what I have:

Sub SelectProd()
sheets("list").select

Dim r1 as Range
Dim r2 as Range
set r1 = range("b41")
set r2 = range("c41")

Sheets("data").select
Rows(r1:r2).select

selection.copy
sheets("data2").select
range("A3").select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

End Sub

When I attempt this I get a 'Compile error' and 'Syntax error'
highlighting 'Rows(r1:r2).select'

I think I'm close but can't figure this out.

Appreciate any help/direction.

Thanks,

Tony B.

 

[Mike Fogleman]

Sub SelectProd()
Dim r1 as Integer
Dim r2 as Integer

r1 = Sheets("list").Range("b41").Value
r2 = Sheets("list").Range("c41").Value

Sheets("data").Activate
Rows(r1:r2).Copy Destination: = Sheets("data2").Range("A3")
Application.CutCopyMode = False
Range("A1").Select
End Sub

You were close!
Mike F

 

[Tony Bender]

Thanks Mike...