Hello All ,
I tried to post this earlier but i cannot find it so Im re-posting now
I have a Workbook with 3 sheets , Sheet 1 is a List of Car makes/years organized my #of consumer requests , Sheet 2 is a top 10 of Sheet 1, Sheet 3 is a list of completed models.
Sheet 1 has data in A/B/C/D , A is the number of requests, B is the Year, C is the make , D is Comments
This sheet is in a table with 2 splicers 1 for make 1 for year, because the list is always 100+ rows. I have co workers that update column A and i use the code below to auto sort by column a Value. ( Data A3:D250 )
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Range("a3").Sort Key1:=Range("a4"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
Sheet 2- Is a list of the top 10 of sheet 1(Data B3:E12) with formulas for sheet 2 to look at sheet 1 so this list changes as my coworkers update sheet 1 ( sheet2 B3 = Sheet1 A3 ) , with at Marco button (G3:G12) to remove it from Sheet 1 and add it to an In process list also on sheet 2 (data B16:E25 ) once the the specific make vehicle is finished i have corresponding Marco buttons to remove that line ( buttons G16:G25) and place it in sheet 3 as an ongoing list.
The first problem I have is when you use the splicer to narrow the 100+ rows of data in my table on sheet one and you update Column A data the VBA code will not re-organize the the table rows by column A values.
Second issue-
if a macro button is hit while the splicer has narrowed the table in sheet1 i get a Run-time Error "1004" no cells were found.
below is the marco for the button click. This what is highlightedwithin the marco - Selection.Delete Shift:=xlUp.
Everything worked as intended prior to the addition of the splicer. I was looking for a convenient way to search or narrow a large table
Range("B4:E4").Select
Range("B4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='Customer Requests '!RC[-1]"
Range("B16:E16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-9
Range("B4:E4").Select
Selection.Copy
Range("B16:E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=6
Sheets("Customer Requests ").Select
Range("A4:D4").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Top 10 ").Select
ActiveWindow.SmallScroll Down:=-18
Range("B2:E2").Select
Selection.AutoFill Destination:=Range("B2:E12"), Type:=xlFillDefault
Range("B2:E12").Select
ActiveWindow.SmallScroll Down:=-6
Range("A1:G1").Select
ActiveWindow.SmallScroll Down:=-6
I tried to post this earlier but i cannot find it so Im re-posting now
I have a Workbook with 3 sheets , Sheet 1 is a List of Car makes/years organized my #of consumer requests , Sheet 2 is a top 10 of Sheet 1, Sheet 3 is a list of completed models.
Sheet 1 has data in A/B/C/D , A is the number of requests, B is the Year, C is the make , D is Comments
This sheet is in a table with 2 splicers 1 for make 1 for year, because the list is always 100+ rows. I have co workers that update column A and i use the code below to auto sort by column a Value. ( Data A3:D250 )
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Range("a3").Sort Key1:=Range("a4"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
Sheet 2- Is a list of the top 10 of sheet 1(Data B3:E12) with formulas for sheet 2 to look at sheet 1 so this list changes as my coworkers update sheet 1 ( sheet2 B3 = Sheet1 A3 ) , with at Marco button (G3:G12) to remove it from Sheet 1 and add it to an In process list also on sheet 2 (data B16:E25 ) once the the specific make vehicle is finished i have corresponding Marco buttons to remove that line ( buttons G16:G25) and place it in sheet 3 as an ongoing list.
The first problem I have is when you use the splicer to narrow the 100+ rows of data in my table on sheet one and you update Column A data the VBA code will not re-organize the the table rows by column A values.
Second issue-
if a macro button is hit while the splicer has narrowed the table in sheet1 i get a Run-time Error "1004" no cells were found.
below is the marco for the button click. This what is highlightedwithin the marco - Selection.Delete Shift:=xlUp.
Everything worked as intended prior to the addition of the splicer. I was looking for a convenient way to search or narrow a large table
Range("B4:E4").Select
Range("B4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='Customer Requests '!RC[-1]"
Range("B16:E16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-9
Range("B4:E4").Select
Selection.Copy
Range("B16:E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=6
Sheets("Customer Requests ").Select
Range("A4:D4").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Top 10 ").Select
ActiveWindow.SmallScroll Down:=-18
Range("B2:E2").Select
Selection.AutoFill Destination:=Range("B2:E12"), Type:=xlFillDefault
Range("B2:E12").Select
ActiveWindow.SmallScroll Down:=-6
Range("A1:G1").Select
ActiveWindow.SmallScroll Down:=-6
