Error in my code please help

[Ramballah]

Hi all,

I have this code in my sheet and it worked perfectly untill i added that line that i marked red. Somehow it wont activate. I tried to use the F8 function and it gave me this error:
Run-time error '1004': Application-defined or object-defined error
I don't know what to do. and i need this line to work because im using formulas in rng1 basically and i only want the values but also the layout of my table.
Hope someone can help

Sub CopyToCorrectRanges()    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    Dim cel As Range
    Dim d As Integer
    Dim i As Integer
    Set rng1 = Range("A1:H102")
    Set rng2 = Range("B3:D102")
    Set rng3 = Range("F3:H102")
    Set rng4 = Range("A1:H1")
    Set cel = Range("B3")
    For d = 0 To 5000
        For i = 0 To 4
            If d = 0 And i = 0 Then i = 1
            If cel.Offset(103 * d, 9 * i) = "" Then
                rng4.ClearContents
                rng4.Value = "Chart " & i + d * 5
                rng1.Copy rng1.Offset(103 * d, 9 * i)
                rng1.Offset(103 * d, 9 * i).ClearContents
[B][COLOR=#ff0000]                rng1.Offset(103 * d, 9 * i).PasteSpecial xlPasteValues[/COLOR][/B]
                rng2.ClearContents
                rng3.ClearContents
                rng4.ClearContents
                rng4.Value = "Main Chart"
                GoTo TheEnd
            End If
        Next i
    Next d
TheEnd:
Application.Calculation = xlCalculationAutomatic
MsgBox "Chart " & i + d * 5 & " has been succesfully made"


End Sub

 

[steve the fish]

Delete the clear contents line. It isnt required.

 

[Ramballah]

Delete the clear contents line. It isnt required.

That did not solve the error i get. Now it copies the entire table including the formulas. I want the entire table without the formulas but with the values.

 

[steve the fish]

So you still get a 1004 error?

 

[Ramballah]

So you still get a 1004 error?

yup

 

[steve the fish]

Merged cells. Try to do it manually. Ill bet it errors. Do yourself a favour and get rid of them. You can use centre across selection if you need to.

 

[Ramballah]

Merged cells. Try to do it manually. Ill bet it errors. Do yourself a favour and get rid of them. You can use centre across selection if you need to.

Oddly enough its not the merged cells because if i remove the rng1.copy rng1.Offset(103 * d, 9 * i) line and make it rng1.copy it works fine.
But i need it to be copied in its whole

 

[steve the fish]

Heres how to do it but get rid of the merged cells. They cause headache after headache.

rng1.Copy
With rng1.Offset(103 * d, 9 * i)
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With

 

[Ramballah]

Heres how to do it but get rid of the merged cells. They cause headache after headache.

rng1.Copy
With rng1.Offset(103 * d, 9 * i)
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With

It gives error 1004 on the xlpastevalues. Also got the error when i removed the merged cell just now.

 

[steve the fish]

What is the text with the error?