HARD:MADE A FUNCTION - HOW DO I LOOP THAT FUNCTION IN MY SUBROUTINE????

[ytjjjtyj]

Hello,
So I made a function which basically calls one named range in my old sheet and then puts that named range in a new sheet.
My problem is I am trying to get all my named ranges from my old sheet onto my new sheet and I can only call one named range at a time in my function. I am struggling to loop the function to do it for many named ranges and to put them in an order one after the other in my new sheet without any overlaps or removals of previously added named ranges. HELP!

[p45cal]

Air code:

myNames = Array("Name1", "Name2", "Name3", "Name4", "Name5")
Set Destn = NewSheet.Range("A1")    'destination of first named range copy
For Each nme In myNames
  With Range(nme)
    .Copy Destn
    Set Destn = Destn.Offset(.Rows.Count)
  End With
Next nme

[ytjjjtyj]

Thank you so much p45cal- this was exactly what I was imagining!
However, I cannot do myRange = Array(....)
I am doing a macro in VBA and the Array function won't work

[p45cal]

Show your actual code.

[ytjjjtyj]

Hello,
I figured it out, I was using a subroutine instead of the function command. Thanks!
Also, do you by any chance know how to add variants to a range? I have multiple variants with 100 rows and varying column numbers and I want to put it all in one range(and in this range I want to distinguish the variants I put in by by skipping a row and putting the next variant in the following row going down and keep doing it for all my variants.) Do you know how I can code that into my subroutine?

I was doing this but I got a type mismatch:

rngDataAggregate = vntDataAggregate_A + vntDataAggregate_B

where rngDataAggregate is set to a specific range in a sheet and vntDataAggregate_A & vntDataAggregate_B are variants with rows and columns.

[p45cal]

in this range I want to distinguish the variants I put in by by skipping a row and putting the next variant in the following row going down and keep doing it for all my variants.) Do you know how I can code that into my subroutine?

Change Set Destn = Destn.Offset(.Rows.Count) to Set Destn = Destn.Offset(.Rows.Count+1)

I was doing this but I got a type mismatch:
rngDataAggregate = vntDataAggregate_A + vntDataAggregate_B
where rngDataAggregate is set to a specific range in a sheet and vntDataAggregate_A & vntDataAggregate_B are variants with rows and columns.

I think you will have to use loops.

[ytjjjtyj]

Air code:

myNames = Array("Name1", "Name2", "Name3", "Name4", "Name5")
Set Destn = NewSheet.Range("A1")    'destination of first named range copy
For Each nme In myNames
  With Range(nme)
    .Copy Destn
    Set Destn = Destn.Offset(.Rows.Count)
  End With
Next nme

Can you show me this in a function with option explicit? Mine is not working.

[p45cal]

This should not be a Function but a Sub.
The code should sit in a standard code-module, not a Worksheet's code module, nor the ThisWorkbook's code-module.
I used air-code because you gave us little information to go on.
NewSheet is an object variable you should earlier have assigned your destination worksheet, it might have been
Set NewSheet=Sheets.add(after:=Sheets(Sheets.count)) ' a brand new added sheet
or Set NewSheet=Sheets("TheNameOfYourDestinationSheetHereNotThisGarbage")
or you don't have to use NewSheet at all:
Set Destn = Sheets("TheNameOfYourDestinationSheetHereNotThisGarbage").Range("A1")
The names in the likes of:
Array("Name1", "Name2", "Name3", "Name4", "Name5")
need to be names of ranges that exist in your workbook. I feel it's unlikely you have named ranges called "Name1", "Name2", "Name3" etc.
So again, air code:

Sub blah()
Dim myNames, Destn As Range, nme, NewSheet As Worksheet
myNames = Array("Name1", "Name2", "Name3", "Name4", "Name5")
Set NewSheet = Sheets("TheNameOfYourDestinationSheetHereNotThisGarbage")    'just one example of many possibilities.
Set Destn = NewSheet.Range("A1")    'destination of first named range copy
For Each nme In myNames
  With Range(nme)
    .Copy Destn
    Set Destn = Destn.Offset(.Rows.Count)
  End With
Next nme
End Sub

If you want a definitive answer, supply a workbook with the necessary named ranges and destination worksheet.

[ytjjjtyj]

p45cal,
You are the best! Thank you so much!