Copy to correct Worksheet, VBA

[mt]

In this code, I want to copy the appropriate "Template" based on the value in based on the value of Ce in the SouceSh. Each Source Sheet is named Data 1 thru 10, with i as the integer variable counter.

I want to copy 20 templates to each worksheet, so I was trying to find a way to count the number of rows for each Ce, so that if they exceed 20, the loop would go to the next Worksheet("Data i")

I would appreciate help with this code. Thanks
Mike

Sub LoadReleases()

Dim i As Integer
Set OutSh = Worksheets("Data" & i)
Set SourceSh = Worksheets("ReleaseLoads")
Set LastRow = Cells(65536, 1).End(xlUp).Row.Offset(1, 0)

For i = 1 To 10
With OutSh
For Each Ce In SourceSh.Range("A2:A" & LastRow)
If Ce.Row.Count > 20 Then
Next i

If Ce.Offset(0, 3).Value = "2SP" Then
Worksheets("Templates").Range("Temp2SP").Copy Destination:=OutSh.Range("A65536").End(xlUp).Offset(1, 0)
ElseIf Ce.Offset(0, 3).Value = "2S" Then
Worksheets("Templates").Range("Temp2S").Copy Destination:=OutSh.Range("A65536").End(xlUp).Offset(1, 0)
ElseIf Ce.Offset(0, 3).Value = "1SP" Then
Worksheets("Templates").Range("Temp1SP").Copy Destination:=OutSh.Range("A65536").End(xlUp).Offset(1, 0)
ElseIf Ce.Offset(0, 3).Value = "1S" Then
Worksheets("Templates").Range("Temp1S").Copy Destination:=OutSh.Range("A65536").End(xlUp).Offset(1, 0)
End If

Next Ce
Next i

End With

End Sub

 

[mt]

Again thanks for the help, we're getting closer.

Now the only Destination Sheet is "Data1". I would like to limit each sheet to the Array. For each of the 10 e's in the array, can we count so that i = e.count in some way or Destination Sheet = "Data"& e.count. I know I can not use count here, but somehow I want the Destination Sheet to change at the next e.

Does that make sense?
Thanks,
Mike

 

[jindon]

Does this give you the idea?

Sub LoadReleasestest3()

Dim Ce As Range
Dim OutSh As Worksheet
Dim i As Integer, a
Set SourceSh = Worksheets("ReleaseLoads")
a = Array(3,23,183)
For i = 0 To UBound(a)
   For Each Ce In SourceSh.Range("A" & a(i) & ":A" & a(i) + 18)
      Select Case Ce.Offset(,3).Value
         Case "2SP", "2S", "1SP", "1S"
            Worksheets("Templates").Range("Temp" & Ce.Offset(, 3).Value).Copy _
            Destination:=Worksheets("Data1").Range("A65536").End(xlUp).Offset(1, 0)
      End Select
   Next Ce
Next

End Sub

Otherwise, tell us what you want to do in words.

 

[mt]

I have the idea, but not experienced enough to understand it. So, thanks for your patience.

Should I change the destination sheet to "Data"& i ?
You have a number of new things for me here that I have not worked with before:
1. What is the significance of Unbound (a)?
2. What does the code a(i) refer to?

You have been great working me through this.
Mike

 

[jindon]

1) a is an array variable which stores three elements taht are 3, 23, 183
Therefore, inside a is like
a(0) = 3
a(1) = 23
a(2) = 183
UBound function returns the upper bound of array so 2 will be returnd,
whereas LBound function returns lower bound which is 0 in this case.

2) You should already understand what a(i) returns by now.

"Data & i"
Can you explain what do you wnat to do with it?

 

[mt]

The idea behind "Data" & i was to allow the copy destination to change with each a(i). In other words, Data from 3 to 22 would copy to "Data1", Data from 23 to 42 would copy to "Data2". "Data10" is the last Data sheet and would be the copy destination for 183 to 202.
I had originally set up i =1 to 10 where "Data" & i were the worksheets and the procedure would loop through each Data Sheet 1 thru 10.

I thought the direction you were going was to have the a(i) where i = 1 to 10, so that the first group in the array would go to "Data1", the next a(i), where i =2 would go to "Data2".

I am certainly and amature at this. All is know is that I continue to learn that I have a long way to go to have a basic understanding of this stuff.

Thanks,
Mike