Copy Application Defined error

[mt]

I have having trouble with this code and receiving the application defined /object defined error message.

The sheet names and range names are correct. What else am I missing?
Thanks
Mike

'Select Template to copy based on Actual Data
Sub CopyTemptoDataSh()
    Dim i As Integer, n As Integer
    Dim LastRow As Long
    Set ws = Worksheets("ActualData")
    Dim Ce As Range, rngCopy As Range
         
    With ws
    
    For n = 2 To 200
    For Each Ce In Worksheets("ActualData").Range("A" & n)      'Ce = start #'s.SpecialCells(xlCellTypeVisible)
    LastRow = Range("A65536").End(xlUp).Offset(1, 0)
        Select Case n                           'Count each loop to determine which "Datai" Sh is copy destination
            Case n = 2 To 22
                i = 1
            Case n = 21 To 40
                i = 2
            Case n = 41 To 60
                i = 3
            Case n = 61 To 80
                i = 4
            Case n = 81 To 100
                i = 5
            Case n = 101 To 120
                i = 6
            Case n = 121 To 140
                i = 7
            Case n = 141 To 160
                i = 8
            Case n = 161 To 180
                i = 9
            Case n = 181 To 200
                i = 10
            Case Else
        End Select
    
    
    Select Case Ce.Offset(, 2).Value
        Case "1S", "1SP", "2S", "2SP"
             Worksheets("Templates").Range("Temp" & Ce.Offset(, 2).Value).Copy Destination:=Worksheets("Data" & i).Range("A65536").End(xlUp).Row
    End Select
Next Ce
Next n
End With
End Sub

 

[pgc01]

Hi Mike

Maybe we can do this by steps. First there are some things you should correct in your code.

- You declare and assign a value to Last Row but you never use it. Either use it or delete it.

- You use a For Each loop. The For Each is for looping through a range but you specify one only cell. If what you want is just one cell use just
Set Ce = Worksheets("ActualData").Range("A" & n)

- The first condition of the selec case (2 to 22) overlaps with the second (21 to 40). I believe the first condition should be (2 to 20).

- In case the first condition is 2 to 20 you can replace the whole select case by

If n >= 2 And n <= 200 Then i = (n - 1) \ 20 + 1

- In the copy statement the destination should be a range and you specify a number(?) instead.

Please make these corrections and if you still have problems post again.

Also give more details, like instead of
- I got an error with this message
better
- I got an error with this message in the statement XXX and the variables involved have the values YYY, ZZZ...

Kind regards
PGC