![]() TargetSheet.Cells(NewRow + 1, 1).Resize(Row, 16).Value = Application.Transpose(Output) TargetSheet.Cells(NewRow, 1).Resize(1, 16).PasteSpecial xlPasteFormats TargetSheet.Cells(NewRow, 1).Resize(1, 16).Value = NewRow = TargetSheet.Cells(, 1).End(xlUp).Row + BufferRows + 1 Output(Column, Row) = Table.ListRows(Index).Range(1, Column).Value If Table.ListRows(Index).Range(1, 1) = Remark Then Remarks.Add Table.ListRows(RemarkIndex).Range(1, 1).Value, Table.ListRows(RemarkIndex).Range(1, 1).Value Set Table = SourceSheet.ListObjects(SourceTableName)įor RemarkIndex = 1 To Set TargetSheet = ThisWorkbook.Worksheets("VBA Output") Set SourceSheet = ThisWorkbook.Worksheets("Sheet1") I did not code around it because I believe the user should know and clean their data first, not rely on code to do so.Ĭonst SourceTableName As String = "Table1" If those aren't removed, the below code will fail. There were only 9 cells with this formula. They are formatted as date, yet they reference the 'Budged Value' and add 30, seemingly arbitrarily. More importantly, in your 'Date Paid' column you have some data which will give you an Overflow error. Please note I did Table the data and referenced it as such in the code. Make sure you change the sheet names to what you have. That being said, it's apparent you want a VBA solution. ![]() It's why I recommended they each go on their own sheet. With that being said, if you still want a VBA solution, I will code one for is exactly the reason you don't want to stack PivotTables or Tables vertically. I'm a very big fan of VBA, but if there is a native solution which will work for you, I'll generally recommend using it instead. Regarding the VBA code you posted, if either of the above solutions work for you it would negate the need for it. These subtotal values will automatically update when you re-apply this feature. It is a fast and easy way to see subtotals by a specific field. I've done this to mimic the example you set in Sheet2. Ensure each change is set for the field 'REMARKS' and you check the columns you want to sum. With the data, on the Data ribbon tab, click the Subtotal button. This is a copy of the data, although it is not in a Table, because the subtotal feature doesn't work with them. There is another sheet titled 'Subtotal'. This is also a general rule for Tables as well. The reason is data will want to grow vertically, and two cannot overlap one another. Instead, having a PivotTable on each sheet would negate this issue. Also, and more importantly, this example has three PivotTables stacked vertically - this is generally not a good idea. I tend to add code to the worksheet housing a PivotTable to automatically refresh their data when the worksheet is activated. You have to refresh them yourself if data is added to the Table. Then, I copied the PivotTable two more times (there are only three PivotTables in this example). Then, I dropped the 'REMARKS' field into the Filter area and set it to the first value. The sheet 'PivotTables' is a PivotTable from the Table as the data source (it's easier to use Tables than standard ranges in my opinion). '.Range("A & lastrow").CurrentRegion.Weight = xlThinĪpplication.Calculation = xlCalculationAutomaticĮnd attached your sample file. Range("A10:P" & lastrow).SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A" & nextrow) Range("A10:A" & lastrow).AutoFilter field:=1, Criteria1:="=" & c.Value Nextrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 3 'loop through input cells and filter and copy results I = WorksheetFunction.CountA(.Range("A1:A9")) ' count number of entries in the input cells 'prepare output sheet to receive new filter results If WorksheetFunction.CountA(Sheet1.Range("A1:A10")) < 1 Then Exit Sub ' test for entries in the input range - exit sub if no entries Sheet1 contains master data, sheet2 contains the filtered data copied from sheet1.Īpplication.Calculation = xlCalculationManual Please find attached a sample with a code I have attempted.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |