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
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
Code:
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