CODEWITHSUNDEEP
excelvba
AKI
AKI

Reputation: 1

Could not set the row property value Invalid property value error - VBA

Below is my code whenever I am clicking on command button of export to excel its popping up me error mentioned in above subject line so please help me for the same. I am really not getting whats wrong with the code the error showing at line .rowsosurce line of below code

Sub show_salepurchasedata()
Dim dsh As Worksheet
Dim sh As Worksheet

Set dsh = ThisWorkbook.Sheets("Sale_Purchase_Worksheet")
Set sh = ThisWorkbook.Sheets("Sale_Purchase_Display")
dsh.AutoFilterMode = False
sh.UsedRange.ClearContents
dsh.Range("H:H").NumberFormat = "D-MMM-YYYY"

'put filter here
dsh.UsedRange.AutoFilter 8, ">=" & Me.txt_startdate.Value, xlAnd, "<=" & Me.txt_enddate.Value
If Me.OptionButton2.Value = True Then
    dsh.UsedRange.AutoFilter 3, "Purchase"
End If
 
If Me.OptionButton3.Value = True Then
    dsh.UsedRange.AutoFilter 3, "Sale"
End If
sh.UsedRange.Clear
dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

'disply data in  list box

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 = "0,120,70,70,70,70,70,70"
    .RowSource = "Sale_Purchase_Display!A2:H" & lr
            
End With



End Sub

Upvotes: 0

Views: 258

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

To be sure that the range address is correctly pasted, it would be good to try replacing of:

.RowSource = "Sale_Purchase_Display!A2:H" & lr

with

.RowSource = Worksheets("Sale_Purchase_Display").Range("A2:H" & lr).Address(external:=True)

But your code should also work. This means that the range itself has a problem. Or the sheet name is wrongly spelled, or lr variable is zero...

For cases when your worksheet name contains spaces (if not wrong spelling, not he case), a better way of using will be:

.RowSource = "'Sale_Purchase_Display'!A2:H" & lr

Upvotes: 1

Related Questions