Count loops to determine copy destination

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
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

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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,929
Office Version
  1. 365
Platform
  1. Windows
Mike

The first thing I see in your existing code is that the syntax for the Select Case is incorrect.

Also, I think but I'm not sure, Ce.Count will always be 1.

The existing code should probably be something like this.
Code:
Sub CopyTemptoDataSh()
Dim i As Long
Dim LastRow As Long

    Set SourceSh = Sheets("Templates")
       
    For Each Ce In Worksheets("ActualData").Range("A" & Rows.Count).End(xlUp)  'Ce = start #'s
        Select Case Ce.Row                           'Count each loop to determine which "Datai" Sh is copy destination
            Case 1 To 20
                i = 1
            Case 21 To 40
                i = 2
            Case 41 To 60
                i = 3
            Case 61 To 80
                i = 4
            Case 81 To 100
                i = 5
            Case 101 To 120
                i = 6
            Case 121 To 140
                i = 7
            Case 141 To 160
                i = 8
            Case 161 To 180
                i = 9
            Case 181 To 200
                i = 10
        End Select
   
        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
Now, as to if that will do exactly what you want I'm unsure because it isn't clear, to me anyway, what that is.:)
 

mt

Board Regular
Joined
Feb 24, 2006
Messages
134
Norie,

Thanks for the quick response. I appologize for the ambiguous question. I am trying to control the copy destination by counting the number of "Ce", so that every 21st Ce, i increases by 1, which changes the copy destination to the next data sheet.

If there are 200 Ce's in the range, each time the code loops through it should adjust by 1.

Does that help make it clearer?
Thanks
Mike
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,929
Office Version
  1. 365
Platform
  1. Windows
Mike

I'm sorry but it still isn't clear, but that's probably just to me.:eek:

What's the exact setup you have?

What sheets exist? Is it Data1, Data2 etc?

What are the values in the range?

PS Did you try the code I posted?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,388
Messages
5,547,661
Members
410,805
Latest member
Ginoji
Top