r/t error 1004

[jim may]

In my app I' have previouly used similar code but for some reason I'm getting a r/t error 1004 as marked below. Can anyone spot the problem?
TIA,
Jim

Sub TransferToNewPricesOnlySheet()
    With Sheets("WorkingReport")
        .Activate
        .Unprotect Password:="john"
    Lr = Range("A" & Rows.Count).End(xlUp).Row
        .Range("A7:L" & Lr).Copy
    End With
    With Sheets("OnlyNewPrices").Range("A7")
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _     'Getting r/t 1004
        SkipBlanks:=False, Transpose:=False
    End With
    ActiveSheet.Protect Password:="john"
    Application.CutCopyMode = False
End Sub

 

[GTO]

Greetings and a Howdy from Arizona Jim,

I'm on a "just barely electric" old laptop running xl2000, and the code as shown worked fine for me. Sorry not of more help

Mark

 

[Colin Legg]

Hi,

What is the error message itself, that should give us a clue?

Do you have any Worksheet_Change / Workbook_SheetChange etc.. event handler code which would cause a problem such as clearing the clipboard / protecting the sheet, etc... when the OnlyNewPrices worksheet's change event is raised on the first pastespecial?

 

[jim may]

My WorkingReport Sheet Code has the following attached to it:

Private Sub CommandButton1_Click()
If Me.CommandButton1.Caption = "Based on Group - Show ONLY Stores With Price Changes" Then
Range("L7").AutoFilter Field:=12, Criteria1:="=CHG", Operator:=xlOr, _
        Criteria2:="=YES"
Me.CommandButton1.Caption = "Remove Price Change Filter"
Else
Range("L7").AutoFilter Field:=12
Me.CommandButton1.Caption = "Based on Group - Show ONLY Stores With Price Changes"
End If
Range("B4").Select
End Sub

Private Sub Worksheet_Calculate()
If Range("L4").Value = 0 Then
Me.CommandButton1.Visible = False
Else
Me.CommandButton1.Visible = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = "$B$4" Then
Exit Sub
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = "$B$4" Then
If Range("B4").Value = "All" Then
    Range("A7").CurrentRegion.AutoFilter Field:=13
    Else
    Range("A7").CurrentRegion.AutoFilter Field:=13, Criteria1:=Range("B4").Value, Operator:=xlAnd
    End If
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

My OnlyNewPrices sheet has NO event code attached..

 

[jasonb75]

Jim, have a look at this page, it gives some information regarding r/t error 1004 in copy / paste macro's.

http://support.microsoft.com/kb/210684

Hope this helps.

 

[GTO]

Greetings Jim,

I have now tried this in 2003, and still no hitches. Further - attempted to replicate the filtering, even tried protecting the destination sheet; am still unable to raise an error.

Have you tried rem'ing the first PasteSpecial, to isolate/see if its the second one that is being balked at?

    With Sheets("OnlyNewPrices").Range("A7")
'    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        'Getting r/t 1004

Mark

 

[Colin Legg]

Hi Jim,

What is the error message that accompanies the error number? Knowing this would be really helpful.

Is there any event handler code in the ThisWorkbook class module?
Are there any merged cells anywhere?


Also, I don't think it will have an impact but this line of code:

Lr = Range("A" & Rows.Count).End(xlUp).Row

should read as:

Lr = .Range("A" & .Rows.Count).End(xlUp).Row