Copy Application Defined error

mt

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

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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,370
Messages
5,675,373
Members
419,567
Latest member
Naveen27mech

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
Top