Trying to apply same range name to different worksheets

[SGBCleve]

I am sure this has been asked before, but I can't find it.
I am trying to take data from one worksheet and bring it into another.
First I am trying to define name ranges in worksheeets 2 through 9 with the names Range1, etc to Range11. (Worksheet2 has a name "Trial23", Worksheet3 is "Trial24", etc to "Trial40").

I tried the following subroutine to name my ranges, but they all come out referring to the same worksheet
I thought about using an array, but wasn't able to get it to work properly either., but saved my array in the comments section.

Sub ranges1to11()
'
' range Macro
' create range1, range2, range3, range4, range5, range6, range7, range8, range9, range10, range11
'


' Dim wsName As Variant

 Dim n As Integer
   
    'wsName = Array("Trial23", "Trial24", "Trial25", "Trial26", "Trial27", "Trial28", "Trial29", "Trial30", "Trial31", "Trial32", "Trial33", "Trial34", "Trial35", "Trial36", "Trial37", "Trial38", "Trial39", "Trial40")
   
    
  
For n = 2 To 19


Sheets(n).Range("B2:E3754").Name = "Range1"
Sheets(n).Range("F2:I3754").Name = "Range2"
Sheets(n).Range("J2:M3754").Name = "Range3"
Sheets(n).Range("N2:Q3754").Name = "Range4"
Sheets(n).Range("R2:U3754").Name = "Range5"
Sheets(n).Range("V2:Y3754").Name = "Range6"
Sheets(n).Range("Z2:AC3754").Name = "Range7"
Sheets(n).Range("AD2:AG3754").Name = "Range8"
Sheets(n).Range("AH2:AK3754").Name = "Range9"
Sheets(n).Range("AL2:AO3754").Name = "Range10"
Sheets(n).Range("AP2:AS3754").Name = "Range11"
Sheets(n).Range("A4:A3754").Name = "Time"


Next n
 End Sub

Can someone help me with the above subroutine? (Of course, I could just go into each sheet and define the names, but it is cumbersome.

I then want to copy data from the first worksheet into the second row of the other worksheets. Unfortunately the first worksheet data is in consectutive rows of four columns wide that I need to bring into row B of each worksheet and each four cells will need to be pasted in adjacent columns of the sheet until the condition is met. I assumeI can use a range with an offset to do that.

Thanks!

 

[DushiPunda]

Worksheets(n).Names.Add Name:="Range1", RefersTo:=Worksheets(n).Name & "!B2:E3754"

Here's an example for the first line you provided. I have no idea if the "RefersTo" will work as I have it, but it's worth a shot.

 

[SGBCleve]

Worksheets(n).Names.Add Name:="Range1", RefersTo:=Worksheets(n).Name & "!B2:E3754"

Here's an example for the first line you provided. I have no idea if the "RefersTo" will work as I have it, but it's worth a shot.

Thanks, I tried, but it didn't help. I will probably wind up appending a trial number to each range.

 

[StephenCrump]

The code below should set up the Range Names you're looking for.

Dim ws As Worksheet
Dim rng As Range
Dim i As Long, j As Long

For i = 2 To 19
    Set ws = Worksheets(i)
    With ws.Names
        Set rng = ws.Range("B2:E3754")
        .Add Name:="Time", RefersTo:="='" & ws.Name & "'!$A$[COLOR=#ff0000][B]4[/B][/COLOR]:$A$3754"  'should this 4 be a 2?
        For j = 1 To 11
            .Add Name:="Range" & j, RefersTo:="='" & ws.Name & "'!" & rng.Offset(, 4 * j - 4).Address
        Next j
    End With
Next i

But I'm not clear what you mean by this. Can you please provide more detail:

I then want to copy data from the first worksheet into the second row of the other worksheets. Unfortunately the first worksheet data is in consectutive rows of four columns wide that I need to bring into row B of each worksheet and each four cells will need to be pasted in adjacent columns of the sheet until the condition is met. I assumeI can use a range with an offset to do that.

 

[SGBCleve]

Thank you very much! I posted the second part as a new Query. I am sure there are a number of mistakes in the new query, but the idea is there.