GEORGE PERRY
03-10-2021, 08:26 AM
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
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