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

 

[Ramballah]

What is the text with the error?

still the same:
Run-time error '1004':
Application-defined or object-defined error

 

[rlv01]

Add another copy statement after the clear contents:

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
                rng1.Copy '<---------------------------------------------- here
                rng1.Offset(103 * d, 9 * i).PasteSpecial xlPasteValues
                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

 

[Ramballah]

Add another copy statement after the clear contents:

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
                rng1.Copy '<---------------------------------------------- here
                rng1.Offset(103 * d, 9 * i).PasteSpecial xlPasteValues
                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

I have tried this myself and does not fix my situation. thanks for the help!
it still gives me the error when im executing the xlpastevalues

 

[steve the fish]

So you did it manually and didnt get an error?

 

[rlv01]

I pasted your exact code and ran it. It fails without the 2nd copy, it works with the 2nd copy statement.

 

[Ramballah]

yeh if i do only

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

Then it works. if i use any other copy or paste before it wont work?

 

[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

Wait this worked. what i did before was first the formats and then the values. but if i do first values and then formats it works! thanks!