Loop to add a value in a specific cell in a worksheet to a specific column in another

[Jay3]

Hi All,

I have a macro which consolidates tasks of various worksheets into one consolidate "TASK VIEW" sheet. It also ignores certain sheets which are related to tasks and clears out and tasks with a status of completed.

The issue I'm having is that certain sheets relate to specific project and the project name on this sheets is stored in a specific cell on these sheets, cell B2.

When I consolidate my tasks the project name is left behind.

Any advice on how to fix this please?

Option Explicit

Public Sub ConsolidateTasks()

  Dim ws As Worksheet
  Dim cws As Worksheet

  Dim iConsolRow As Long
  Dim iLastRow As Long
  Dim iTaskRow As Long

  Set cws = ThisWorkbook.Sheets("Task View by Date")
  iLastRow = cws.Cells(cws.Rows.Count, "B").End(xlUp).Row + 1
  cws.Range("B7:J" & iLastRow).ClearContents

  iConsolRow = 7

   For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
      Case "New Project Requiring Board", "Task View by Date", "Project Board Template", "Lookups"
      Case Else
         iLastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
         For iTaskRow = 7 To iLastRow
           iConsolRow = iConsolRow + 1
           If ws.Cells(iTaskRow, "M") <> "Yes" Then
               ws.Cells(iTaskRow, "B").Resize(1, 9).Copy Destination:=cws.Cells(iConsolRow, "B")
               cws.Rows(iConsolRow).RowHeight = cws.Rows(6).RowHeight
           End If
         Next iTaskRow
    End Select
  Next ws


  With cws.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("H7:H" & iConsolRow), SortOn:=xlSortOnValues, _
          Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("B7:J" & iConsolRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With

  MsgBox "Done:  All entries successfully copied to Task View by Date" & Space(10), _
         vbOKOnly + vbInformation, "Webteam Deliverables"

End Sub

I would like the board name to be repeated next to the individual task that is pulled in from the project worksheet.

 

[Jay3]

This is an individual project with the board name in B2, this one is called "Ad-hoc Tasks"

This is the consolidated view, the Project / Board name go between Task To Dos and Notes.

 

[Jay3]

Actually I could just add a column for board name in the project boards that might help keep it simple.