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?
I would like the board name to be repeated next to the individual task that is pulled in from the project worksheet.
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?
VBA Code:
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.