New to Excel-VBA: Run Time Error "91": Object Variable or With block variable not set

Question

I'm new to Excel-VBA so please excuse my ignorance. ;)

I'm actually trying to run a program on the selected sheet from a UserForm. The program would then get a value from the selected sheet which would import it to a newly created sheet. Sheet18 is a sheet template it will be copied to the new sheet.

This are the codes:

Private Sub CommandButton1_Click()
UserForm1.Hide
  Dim wsUserSel As Worksheet
  Dim ws As Worksheet
  Dim ExFund As Variant

    On Error Resume Next
    Set wsUserSel = ListBox1.Value
    Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
    On Error GoTo 0
        With wsUserSel
            ExFund = .Cells(107, 4).Value
        End With
        Sheet18.Range("A1:K126").Copy
        ws.Name = InputBox("Please input the name of new worksheet.", ThisWorkbook.Name)
        ws.Activate
        Range("A1").Select
        ActiveSheet.Paste
        ActiveSheet.Cells(29, 2) = ExFund
        ActiveSheet.Cells.EntireColumn.AutoFit
        Application.CutCopyMode = False
End Sub

This code returns an error

Run Time Error "91": Object Variable or With block variable not set

because of this part of the code:

    With wsUserSel
        ExFund = .Cells(107, 4).Value
    End With

I have tried putting it inside With or using wsUserSel directly but I can't seem to get it working.

What am I doing wrong?

YowE3K · Accepted Answer · 2017-12-06 03:35:24Z

1

Your On Error Resume Next statement is masking the error you are getting on the line saying

Set wsUserSel = ListBox1.Value

That line will fail because ListBox1.Value will not be a Worksheet object. I assume it is a String that specifies which worksheet you want to use.

So change the section saying:

On Error Resume Next
Set wsUserSel = ListBox1.Value
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
On Error GoTo 0

to be:

Set wsUserSel = Worksheets(ListBox1.Value)
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))

answered Dec 6, 2017 at 3:35

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gemerson Bagtas Over a year ago

Thank you YowE3k. Your answer was spot on!

Collectives™ on Stack Overflow

New to Excel-VBA: Run Time Error "91": Object Variable or With block variable not set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related