PDA

View Full Version : Sleeper: Copy filtered table



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

snb
04-19-2016, 01:03 PM
Please, use code tags !


Sub M_snb()
sheets.add(,sheets("summary")).name="customer"

with Sheets("FabricatedParts").ListObjects("Parts").Range
.autofilter 5, InputBox("Which Customer")
.Copy sheets("customer").cells(1)
.autofilter
end with

with sheets("customer")
.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name ="CustomerName" & "Table"
.Name = .Range("E2")
End With
End Sub

neilm4247
04-20-2016, 12:50 PM
I can understand why your code is in some ways better than mine but the results are that it copies the whole table not just the filtered results. In addition, mine is once again working perfectly which begs the original question of why now and not yesterday? Sorry about not tagging the code.

snb
04-20-2016, 01:13 PM
it copies the whole table not just the filtered results

It doesn't; it only copies the filtered result.

neilm4247
04-20-2016, 02:54 PM
15970Screenshot of table built with your code. Not sure you can see but it includes all parts from all customers, not just the parts from the one customer.