run time error 1004 Pastespecial method of range class failed

[GEORGE PERRY]

please help me on this
pastespecial method of range class failed
run time error 1004



Sub Show_Sale_Purchase_Data()

Dim dsh As Worksheet
Dim sh As Worksheet

Set dsh = ThisWorkbook.Sheets("Sale_Purchase")
Set sh = ThisWorkbook.Sheets("Sale_Purchase_Display")


dsh.AutoFilterMode = False

dsh.Range("H:H").NumberFormat = "dd-mm-yyyy"


'''''''''Put filter here''''''

dsh.UsedRange.AutoFilter 8, ">=" & Me.txt_Start_Date.Value, xlAnd, "<=" & Me.txt_End_Date.Value

If Me.OptionButton3.Value = True Then

dsh.UsedRange.AutoFilter 3, "Purchase"
End If

If Me.OptionButton2.Value = True Then
dsh.UsedRange.AutoFilter 2, "Sale"
End If

sh.UsedRange.Clear

dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

dsh.AutoFilterMode = False

'''''''''Display Data in Listbox'''''''

Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox2
.ColumnCount = 8
.ColumnHeads = True
.ColumnWidths = "30,100,50,50,50,50,50,50"
.RowSource = sh.Name & "!A2:H" & lr

End With

End Sub

[SamT]

:dunno:
Try

dsh.Cells.SpecialCells(xlCellTypeVisible).Copy
sh.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

[Dave]

I agree with SamT to limit the copy area. I was going to suggest coding the specific range. UsedRange includes any cell on the sheet that may have been used or have format changes made ever. Copying/pasting that whole area might cause a crash for whatever reason. Beyond that, maybe it's just a syntax thing? Dave

[GEORGE PERRY]

thanks very much for the directions