CODEWITHSUNDEEP
vbaexcelsyntaxnamed-ranges
A.Webb
A.Webb

Reputation: 11

Create dynamic range and store named ranges

I generate Range A in Worksheet("Model"). I move these values to Worksheet("Data"), so that I can start to store all this data. I will have have another tab ("SheetC") that displays two user-selected ranges from the stored data. e.g Range A from day 1 and Range A from day 2. In the process of moving data to store in "Data" i want to place it directly below the last. I have a counter that I reference in "SheetC" that increases each time new data is placed in the sheet (i).

I would ideally like each Range A in "Data" to be named as a different Range so when the user selects a certain range I can just simply display that range by calling it, rather than having to lookup a value in the stored data and using offset.

Dim rngData_Total_a As Range
Dim rngData_Total_b As Range
Dim i As integer

i = Sheets("SheetC").Range("AJ3").Value
Set rngData_Total_a = Sheets("Model").Range("R14:BH270")
Set rngData_Total_b = Sheets("Data").Range(Cells(3 + 270 * i, 3), Cells(3 + 257 * (i + 1), 3))

rngData_Total_b.Value = rngData_Total_a.Value

I keep getting application/object defined error. Can one simply set up a range as such? Or is there a more efficient way to do this? Ideally I would like a dynamic Range name also, so that rngData_Total_b changes its name with each new Range. Basically the process would replicate me pre-defining x numbers of ranges as different names and different locations in "Data", considering I do not know the number of Ranges I will store there.

Thanks

Anthony

Upvotes: 1

Views: 77

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Try test

Sub test()
Dim rngData_Total_a As Range
Dim rngData_Total_b As Range
Dim i As Integer
Dim rngT As Range

i = Sheets("SheetC").Range("AJ3").Value
Set rngData_Total_a = Sheets("Model").Range("R14:BH270")
With Sheets("Model")
    Set rngT = .Range("c" & Rows.Count).End(xlUp)(2)
    Set rngDaata_total_b = rngT.Resize(257, 43)
End With
'Set rngData_Total_b = Sheets("Data").Range(Cells(3 + 270 * i, 3), Cells(3 + 257 * (i + 1), 3))

rngData_Total_b.Value = rngData_Total_a.Value
End Sub

Upvotes: 0

Related Questions