I am creating a tool to track project labor costs across multiple sites at our company. I ran into an issue implementing this as not all projects are worked on each month. As such I have had to modify to the tool to account for all projects each month regardless of if they were worked on.

I need to search for 17 individual strings in a PivotTable and if the string is found copy all cells below that string into another tab which calculates the cost. This find and copy function is where I am stuck.

I created some code that copied the data that was specific to a location of the string, but it did not account for the fact the project title (string) will be in the same place in this table each month when this is run. The position of where this data will be copied however does not change.

Appreciate any guidance to get me started...

1 Answers

Community On

I am basing this off of the code that you posted in your comment. I found the desired range using:

  1. First Row of the data below the header (assumed to be row 4)
  2. Last Row of data (calculated by finding the largest row that has a cell with data)
  3. Column of the project title match

Sub TRM0000()

Dim Ws As Worksheet Dim firstRow As Long Dim lastRow As Long Dim column As Long Dim Calc As Range Set Ws = Sheets("PivotTable") firstRow = 4 lastRow = Ws.Cells.Find(What:="*", SearchDirection:=xlPrevious).Row column = Ws.Range("C3:Z3").Find(What:="TRM-0000", LookIn:=xlValues, LookAt:=xlWhole).column Set Calc = Ws.Range(Ws.Cells(firstRow, column), Ws.Cells(lastRow, column)) Calc.Copy Sheets("Cost Calc").Range("B5").PasteSpecial xlValues End Sub

This should be a solution for this specific project title. However, you said you have 17! Instead of recreating this code for each, I would recommend storing the 17 project titles (could be in a tab of the workbook or enumerated in the VBA code), creating a for loop to run through that list, and making the project title and output range references dynamic in the VBA code.