neilm4247
04-19-2016, 12:17 PM
I am using Excel 2016
I have the code below which worked perfectly the day I created it. Now I get an error on the line in bold. The error is 91: Object variable or with block variable not set. How was it set the other day and not today?
Part 2 of my question is once this code is run and I have a new table on another sheet with a new name how do I assign a variable to the new table name?
Thank you in advance.
Sub CreateCustomerTable()
Dim CustomerName As String
CustomerName = InputBox("Which Customer")
Sheets("FabricatedParts").Activate
ActiveSheet.ListObjects("Parts").Range.AutoFilter Field:=5, Criteria1:= _
CustomerName
ActiveSheet.AutoFilter.Range.Copy
Sheets.Add After:=Sheets("Summary")
ActiveSheet.Paste
Application.CutCopyMode = False
'Insert table and name it.
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
"CustomerName" & "Table"
With ActiveSheet
.Name = Range("E2").Value
End With
End Sub
I have the code below which worked perfectly the day I created it. Now I get an error on the line in bold. The error is 91: Object variable or with block variable not set. How was it set the other day and not today?
Part 2 of my question is once this code is run and I have a new table on another sheet with a new name how do I assign a variable to the new table name?
Thank you in advance.
Sub CreateCustomerTable()
Dim CustomerName As String
CustomerName = InputBox("Which Customer")
Sheets("FabricatedParts").Activate
ActiveSheet.ListObjects("Parts").Range.AutoFilter Field:=5, Criteria1:= _
CustomerName
ActiveSheet.AutoFilter.Range.Copy
Sheets.Add After:=Sheets("Summary")
ActiveSheet.Paste
Application.CutCopyMode = False
'Insert table and name it.
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
"CustomerName" & "Table"
With ActiveSheet
.Name = Range("E2").Value
End With
End Sub