arrays - Why does my attempt at referring to named ranges with a variant variable to which I (for-next)-store the names of ranges fail?

Question

Apologies for the confusing title. I have multiple named ranges and I am trying to create a macro that would allow me to copy a certain row of each one of the ranges and paste it to however many following rows in each range as the user pleases. I will create 20 different macros depending on from which row the user would like to copy the selections, but for now I'm testing it with row 3.

The issue however is that I managed to store the name of the first range in a variant variable. However I have only succeeded in selecting the row with application.goto-function, but have failed in trying to copy certain rows of it. For example when I try to use the Range.(aspect(the name of my variable))-function I receive an error method 'range' of object '_Global' failed. I have also tried to create a new array based on the range that the name stored in the variable should bring but have failed in that too.

Public Sub copy_window_3()

Sheets("options").Select

ccArr = Range("ccAr").Value

copy_window rowstart:=3, copyquant:=Application.WorksheetFunction.Index(ccArr, rowstart)

Exit Sub

End Sub



Public Sub copy_window(rowstart As Double, copyquant As Variant)

Dim aspect As Variant
Dim copycounter As Integer
Dim aspcounter As Double
Dim ccArr() As Variant
Dim aspAr() As Variant


aspAr = Range("aspAr").Value

For aspcounter = 1 To UBound(aspAr, 1)

aspect = Application.WorksheetFunction.Index(aspAr, aspcounter)

Range(aspect).Rows(rowstart).Copy

    For copycounter = 1 To copyquant

    Range(aspect).Rows(rowstart + copyquant).PasteSpecial xlPasteValues

    Next copycounter

Next aspcounter


End Sub

In case the code looks confusing: The macro first uses ccArr and rowstart variables to figure out from which row to start and how many rows to go. I intended to loop through each range name which are stored in aspAr and then copy the desired row and then loop and paste to each row that the user wishes.

Pardon me for the silly question, but I tried looking for the answer online for hours with no luck.

Thanks in advance for the help!

Cheers.

question from:https://stackoverflow.com/questions/66049962/why-does-my-attempt-at-referring-to-named-ranges-with-a-variant-variable-to-whic

Answer 1

Waitting for answers