Fout 1004 tijdens uitvoering: Methode PasteSpecial van klasse Range mislukt.

Hawkie003 · 21 jan 2016

Goedenmiddag,

Ik heb de volgende code staan en krijg daar sinds vandaag de volgende foutmelding bij:
"Fout 1004 tijdens uitvoering: Methode PasteSpecial van klasse Range mislukt."

Weet iemand waardoor ik deze foutmelding krijg, nog beter hoe ik dit kan oplossen? De code is vrij omslachtig, maar werkte vreemd genoeg tot vandaag.

Alvast bedankt!

Code:

Sub Vue_Input_Input()
    Application.ScreenUpdating = False
    
    If Sheets("Vue_Input").Range("C11").Value = "" Then
            MsgBox "Fill in your name."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C12").Value = "" Then
            MsgBox "Fill in the Vue cinema of rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C16").Value = "" Then
            MsgBox "Fill in the date of rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C18").Value = "" Then
            MsgBox "Fill in the rental type."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C19").Value = "" Then
            MsgBox "Fill in the company or person of rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C20").Value = "" Then
            MsgBox "Fill in the contact for the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C21").Value = "" Then
            MsgBox "Fill in the address of the company or person of rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C22").Value = "" Then
            MsgBox "Fill in the zippcode and city of the company or person of rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C24").Value = "" Then
            MsgBox "Fill in the email address of the contact."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C25").Value = "" Then
            MsgBox "Fill in the form of payment."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C27").Value = "" Then
            MsgBox "Fill in the start time of the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C28").Value = "" Then
            MsgBox "Fill in the end time of the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C29").Value = "" Then
            MsgBox "Fill in the cinemas used for the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C31").Value = "" Then
            MsgBox "Fill in if there is a break during the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("F16").Value = "" Then
            MsgBox "Fill in if the cinema of rental is notified."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("F18").Value = "" Then
            MsgBox "Fill in if the rental is optional or definitive."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("F19").Value = "" Then
            MsgBox "Fill in the number of persons attending the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("F28").Value = "" Then
            MsgBox "Fill in the movies or content used for the rental."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("C31").Value = "Yes" Then
    If Sheets("Vue_Input").Range("F31").Value = "" Then
            MsgBox "Fill in by who the F&B is arranged."
            Exit Sub
    End If
    End If
    If Sheets("Vue_Input").Range("B34").Value = "" Then
            MsgBox "Fill in the arrangements made with the tenant."
            Exit Sub
    End If
    If Sheets("Vue_Input").Range("F36").Value = "" Then
            MsgBox "Fill in the agreed price of the rental."
            Exit Sub
    End If
    
    Dim x
    x = InputBoxDK("Fill in the required password.", "Password Required")
    If x = "" Then End
    If x <> Sheets("Data").Range("F60") Then
        MsgBox "You didn't enter the correct password."
        End
    End If
    
    Sheets("Vue_Input").Select
    ActiveSheet.Unprotect Password:="JTAMER"
    ActiveWorkbook.Unprotect Password:="tj3rkie"
    Sheets("All_Rentals").Visible = xlSheetVisible
    Sheets("All_Rentals").Select
    ActiveSheet.Unprotect Password:="JTAMER"
    Sheets("All_Rentals").Range("A8").Select
    Sheets("All_Rentals").Range("A8").Activate
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    
    Sheets("Vue_Input").Select
    Range("B34").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("AA8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Vue_Input").Select
    Range("C10").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("F8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C11").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("E8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C12").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("D8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C13").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("O8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C15").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("C8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Vue_Input").Select
    Range("C16").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("L8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Vue_Input").Select
    Range("C18").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("M8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C19").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("G8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C20").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("H8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C21").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("I8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C22").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("J8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C24").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("K8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C25").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("N8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C27").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("R8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C28").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("S8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Vue_Input").Select
    Range("C29").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("P8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("C31").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("W8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Vue_Input").Select
    Range("F10").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("Y8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("F13").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("Z8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("F16").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("F18").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("V8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("F19").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("U8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Vue_Input").Select
    Range("F27").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("T8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("F28").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("Q8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Vue_Input").Select
    Range("F31").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("X8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Vue_Input").Select
    Range("F36").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("AB8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("All_Rentals").Select
    Range("B7:AG2506").Select
    Application.AddCustomList ListArray:=Array("Definitive", "Optional", _
        "Cancelled")
    ActiveWorkbook.Worksheets("All_Rentals").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("All_Rentals").Sort.SortFields.Add Key:=Range( _
        "D7:D2506"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("All_Rentals").Sort.SortFields.Add Key:=Range( _
        "V7:V2506"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Definitive,Optional,Cancelled", DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("All_Rentals").Sort.SortFields.Add Key:=Range( _
        "L7:L2506"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("All_Rentals").Sort
        .SetRange Range("B6:AG2506")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("2507:2507").Select
    Selection.Delete Shift:=xlUp
    
    Sheets("Vue_Input").Select
    Range("B34,C11:C12,C16,C18:C22,C24:C25,C27:C29,C31,F16,F18:F19,F29,F31,F36").Select
    Selection.ClearContents

    
    Sheets("All_Rentals").Select
    ActiveSheet.Protect Password:="JTAMER", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
    ActiveSheet.EnableSelection = xlNoSelection
    Sheets("Vue_Input").Select
    ActiveSheet.Protect Password:="JTAMER"
    Sheets("All_Rentals").Visible = xlVeryHidden
    ActiveWorkbook.Protect Password:="tj3rkie"
    ActiveWorkbook.Save
    Sheets("Vue_Input").Select
    Range("C11").Select
    Application.ScreenUpdating = True
End Sub

snb · 21 jan 2016

1. Sjonge
2. welke regel geeft de foutmelding ?
3. ben je bekend met Userforms ?
4. Vermijd 'Select' en 'Activate'
5. poets eerst de code eens op en daarna praten we verder.

edmoor · 21 jan 2016

Loop er eens doorheen met F8 (Debug mode). Dan kom je erachter welke regel die fout teweeg brengt.

Hawkie003 · 21 jan 2016

Excel verwijst bij de foutmelding naar het deel in blauw aangegeven.

Code:

  Sheets("Vue_Input").Select
    Range("B34").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("AA8").Select
    [COLOR="#0000CD"]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False[/COLOR]

Cobbe · 21 jan 2016

Code:

 Sheets("Vue_Input").Select
    Range("B34").Select
    Selection.Copy
    Sheets("All_Rentals").Select
    Range("AA8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

kan je vervangen door:

Code:

' Sheets("All_Rentals").Range("AA8") = .Range("B34").Value

Zo kun je al heel wat wegwerken.

PS: Och ja nog bedankt voor de respons op vorige vraag van u.

Fout 1004 tijdens uitvoering: Methode PasteSpecial van klasse Range mislukt.

Hawkie003

Gebruiker

snb

Verenigingslid

edmoor

Inventaris

Hawkie003

Gebruiker

Cobbe

Giga Honourable Senior Member †

Nieuwste berichten

Wij waarderen jouw privacy