In the following code, I want to limit the number of "templates" that get copied to each Data sheet to 20. I have 10 Data sheets to allow for a maximum of 200 templates. I tried to set a counter = i in a select case statement, where the code would count the each item in this range, using Ce.Count. I thought this would count where the code was in the loop, so that the first 20 items would go to Data1, where i=1, and so forth.
Is there a way to count the items in this range (Ce) so that it resets which each loop (next Ce)?
The other issue is selecting the right case for which template to copy. I am confused here because I am referencing 3 different worksheets in this procedure, Data &i, ActualData, and Templates. I need some help with this code. Thank you.
Mike
Is there a way to count the items in this range (Ce) so that it resets which each loop (next Ce)?
The other issue is selecting the right case for which template to copy. I am confused here because I am referencing 3 different worksheets in this procedure, Data &i, ActualData, and Templates. I need some help with this code. Thank you.
Mike
Code:
Sub CopyTemptoDataSh()
Dim i As Integer, a
Dim LastRow As Long
Set SourceSh = Sheets("Templates")
For Each Ce In Worksheets("ActualData").Range("A" & Rows.Count).End(xlUp) 'Ce = start #'s
With Worksheets("ActualData")
Select Case Ce.Count 'Count each loop to determine which "Datai" Sh is copy destination
Case Ce.Row.Count >= 1 And Ce.Count <= 20
i = 1
Case Ce.Count >= 21 And Ce.Count <= 40
i = 2
Case Ce.Count >= 41 And Ce.Count <= 60
i = 3
Case Ce.Count >= 61 And Ce.Count <= 80
i = 4
Case Ce.Count >= 81 And Ce.Count <= 100
i = 5
Case Ce.Count >= 101 And Ce.Count <= 120
i = 6
Case Ce.Count >= 121 And Ce.Count <= 140
i = 7
Case Ce.Count >= 141 And Ce.Count <= 160
i = 8
Case Ce.Count >= 161 And Ce.Count <= 180
i = 9
Case Ce.Count >= 181 And Ce.Count <= 200
i = 10
Case Else
End Select
End With
Select Case Ce.Offset(, 3).Value
Case "1S", "1SP", "2S", "2SP"
Worksheets("Templates").Range("Temp" & Ce.Offset(, 2).Value).Copy Destination:=Worksheets("Data" & i).Range("A65536").End(xlUp).Row.Offset(1, 0)
End Select
Next Ce
End Sub