Using Spreadsheet object model and dynamic ranges in VBA

I'm trying to create a dropdown spreadsheet using the VBA spreadsheet object model in excel that will pull in data from the regular excel spreadsheet. The spreadsheet object will open up when a user clicks a command button in the regular spreadsheet. I've figured out how to create the spreadsheet object and have it pull in data from the regular spreadsheet but cant get dynamic ranges to work for me. The data in the regular spreadsheet will change. I've created a named range that accurately captures the changing data in the the regular spreadsheet but I can't get the dropdown spreadsheet to correctly pull in from this range. It will work if I manually put in something like range("A1:G10") in the place of range("range_value") in my code but I need this to change dynamically with the dynamic named range, testrange1, in the regular spread sheet. I created the dynamic range, 'range_value', in my code but it doesn't work. Range_value needs to change with the dynamic range 'testrange1'.

Here is the full code...
[code]Private Sub UserForm_Initialize()
Dim countrow As Integer
Dim countcol As Integer
Dim range_value As Object

countrow = Range("testrange1").Rows.Count
countcol = Range("testrange1").Columns.Count
Set range_value = Sheet1.Range(Sheet1.Cells(4, 4), Sheet1.Cells(3 + countrow, 3 + countrow))

Spreadsheet1.Sheets("Sheet1").Range("range_value").Value = Sheets("RFF Data").Range("testrange1").Value
End Sub[/code]

Please Help!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!