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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.:)
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top