Copying Multiple Named Ranges from one sheet to Another

[fienhagus]

Hello Everyone!

This is my first question posted, so sorry if it is not aesthetically pleasing.

I am in the process of making a sub or two to create named ranges based on data in one worksheet, and then transfer it to another worksheet. I have working code below, but I do not wish to type out all of the named ranges and believe there is a way to automate this process.

Here's what I have thus far:

Sub NR()

'Creates named ranges based on parameter name, then assigns the data below that parameter to that name.
For i = 1 To 369
For j = 1 To 369
ActiveWorkbook.Names.Add _
Name:=Sheets("Sheet1").Cells(1, i).Value & "Range", _
RefersTo:=Sheets("Sheet1").Cells(j, i)
Next j
Next i

End Sub


Sub CopyPasta()


Sheets("Sheet1").Columns(Range("howRange").Column).Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

End Sub

Sheet 1 in my workbook is a sheet that has 100's of columns of data, where each of those columns is a named range. It has rows 2:44 of data for each of those columns, in which the named range is assigned to.

In the Sub CopyPasta(), I paste the first named range of sheet 1 onto sheet 2. This works well, but would be very annoying to add a section like this for every named range in sheet 1.

How should I go about automating this process?

Thank you for your time!

 

[JLGWhiz]

If your columns are consecutive you could use a For loop. Assume Columns 1 To 100.

For i = 1 To 100
	With ActiveWorkbook.Sheets("Sheets1")
		.Cells(1, i).Resize(44, 1).Copy ActiveWorkbook.Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
	End With
Next

 

[fienhagus]

Thank you for the reply! This would be a temporary fix, but unfortunately there will be added parameters as time goes on, and I am not certain that they will be added at the end of the last parameter (could be added in the middle due to grouping issues).

 

[JLGWhiz]

In your original post you added 369 names. If you only want to select 100 specific non-consecutive names, there is no way around typing them out somewhere. Computers are fast, but stupid. They are only as logical as the programmer. You could take any 100 consecutive columns and copy the named ranges with the code previously posted. But if you want to copy non-adjacent columns, you have to have a means to pass that information to the compiler, such as a list in a specified column on a specified sheet, or an array, or some other means of referral that can be logically coded.