![]() ![]() 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. When the pointer becomes a move pointer, drag the rows or columns to another location.I have a PivotTable with a slicer that has 5 filters. When the pointer becomes a move pointer, drag the rows or columns to another location.Ĭopy and insert Hold down SHIFT and CTRL while you point to the border of the selection. Press CTRL+Z if you don't want to replace a row or column.Ĭut and insert Hold down SHIFT while you point to the border of the selection. When the pointer becomes a copy pointer, drag the rows or columns to another location. Excel doesn't warn you if you are going to replace a column. Press Cancel to avoid replacing.Ĭopy and replace Hold down CTRL while you point to the border of the selection. Excel warns you if you are going to replace a column. When the pointer becomes a move pointer, drag the rows or columns to another location. Select the row or column that you want to move or copy.Ĭut and replace Point to the border of the selection. By default, drag-and-drop editing is turned on so that you can use the mouse to move and copy cells. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |