SlicerItems("CE Tractor Loader Burlington").Selected = CETractorLoaderBurlington Sub FilterSlicer_Model_Family_Description(CETractorLoaderBurlington As Boolean, _ĭraperPickupHeadersBurlington As Boolean, _ Cells(.Rows.Count, 1).End(xlUp).Offset(1) EntireRow.Columns("D").Value = ColumnDValue EntireRow.Columns("AG").Value = "Final Customer" Cells(.Rows.Count, DestColumn).End(xlUp).Offset(1) With Workbooks("QA Matrix Mar 2019 copy.xlsm").Worksheets("Plant Sheet") Cells(.Rows.Count, SourceColumn).End(xlUp)).Value With Workbooks("Warranty Template.xlsm").Worksheets("PivotTable") Sub AppendColumnDataToPlanetSheets(SourceColumn As Variant, DestColumn As Variant) Sub InsertData()įilterSlicer_Model_Family_Description False, True, False, False, False, False, FalseįilterSlicer_Model_Family_Description True, False, False, False, False, False, FalseįilterSlicer_Model_Family_Description False, False, True, False, False, False, False You can also test the column assignments individually. You can easily test the various slicer filters without having to run the whole code.
COPY AND PASTE SLICER IN EXCEL CODE
Delegating tasks to other subroutines makes the project easier to read, modify and debug.Ĭonsider the code below. Ideally, a subroutine should perform only one or two tasks. Repeating code within a subroutine is a sign that the procedure needs to be broken up into multiple subroutines. The should be the same code for all slicers which is why I assume looping would be a more efficient way of setting this up WsDest.Range("D" & DefDestLastRow & ":D" & NewLastRow).Value = "Corn" SlicerItems("Corn Headers Burlington").Selected Then SlicerItems("Corn Headers Burlington").Selected = True WsDest.Range("D" & DefDestLastRow & ":D" & NewLastRow).Value = "TL" SlicerItems("CE Tractor Loader Burlington").Selected Then SlicerItems("CE Tractor Loader Burlington").Selected = True SlicerItems("Backhoes Case Burlington").Selected = False WsDest.Range("D" & DefDestLastRow & ":D" & NewLastRow).Value = "TLB" WsDest.Range("AG" & DefDestLastRow & ":AG" & NewLastRow).Value = "Final Customer" NewLastRow = wsDest.Cells(, 10).End(xlUp).Row WsDest.Range("S" & DefDestLastRow).PasteSpecial Paste:=xlPasteValues WsCopy.Range("E5:E" & DefCopyLastRow).Copy
WsDest.Range("P" & DefDestLastRow).PasteSpecial Paste:=xlPasteValues WsCopy.Range("D5:D" & DefCopyLastRow).Copy WsDest.Range("M" & DefDestLastRow).PasteSpecial Paste:=xlPasteValues WsDest.Range("L" & DefDestLastRow).PasteSpecial Paste:=xlPasteValues
WsCopy.Range("B5:B" & DefCopyLastRow).Copy WsDest.Range("J" & DefDestLastRow).PasteSpecial Paste:=xlPasteValues WsCopy.Range("A5:A" & DefCopyLastRow).Copy SlicerItems("Backhoes Case Burlington").Selected Then SlicerItems("Grain Headers Burlington").Selected = False SlicerItems("Forklift Case Burlington").Selected = False SlicerItems("Draper & Pickup Headers Burlington").Selected = False SlicerItems("Dozer Case Calhoun Burlington").Selected = False SlicerItems("Corn Headers Burlington").Selected = False SlicerItems("CE Tractor Loader Burlington").Selected = False SlicerItems("Backhoes Case Burlington").Selected = True With ActiveWorkbook.SlicerCaches("Slicer_Model_Family_Description") Copy & Paste Data For Each Filter Selection Find first blank row in the destination range based on data in column DĭefDestLastRow = wsDest.Cells(, 10).End(xlUp).Offset(1, 0).Row Find last used row in the copy range based on data in column AĭefCopyLastRow = wsCopy.Cells(, 1).End(xlUp).Offset(-1, 0).Row Set wsDest = Workbooks("QA Matrix Mar 2019 copy.xlsm").Worksheets("Plant Sheet") Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable") 'Set variables for copy and destination sheets I've set up the code to work for each filter so far with completely rewriting/copying each code for each filter and everything works but it seems like a lot of unnecessary code Sub InsertData()ĭim wsCopy As Worksheet, wsDest As Worksheetĭim DefCopyLastRow As Long, DefDestLastRow As Long I'm looking to have these filters to each be selected individually one by one and run a copy/paste code for every filter selected.
I have a PivotTable with a slicer that has 5 filters.
0 kommentar(er)
