Hi,
I had an old workbook which consolidated team tasks into one sheet. I am trying to tweak it for another workbook I am building. Here's the original code I had.
The problem is on the original workbook I had the consolidated sheet and the team sheets (the number of team sheets didn't change) so when consolidating the team sheets the macro ignored the consolidated sheet (cws) when copying the tasks. In my new workbook, I have other worksheets I need to ignore (which don't contain tasks) and also the number of worksheets containing individuals tasks can grow each time a new project is added.
So in summary when copying and pasting tasks to the consolidated tab it needs to ignore a predefined set of worksheets and then just keep going through all of the other worksheets however many there might be.
Any help with this would be much appreciated.
Thanks
I had an old workbook which consolidated team tasks into one sheet. I am trying to tweak it for another workbook I am building. Here's the original code I had.
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 = 5
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> cws.Name Then
iLastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For iTaskRow = 6 To iLastRow
iConsolRow = iConsolRow + 1
ws.Cells(iTaskRow, "B").Resize(1, 4).Copy Destination:=cws.Cells(iConsolRow, "B")
cws.Rows(iConsolRow).RowHeight = cws.Rows(6).RowHeight
Next iTaskRow
End If
Next ws
With cws.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("E6:E" & iConsolRow), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("B6:E" & iConsolRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
MsgBox "Done: " & CStr(iConsolRow - 5) & " entries copied to team worksheet" & Space(10), _
vbOKOnly + vbInformation, "Team Deliverables"
End Sub
The problem is on the original workbook I had the consolidated sheet and the team sheets (the number of team sheets didn't change) so when consolidating the team sheets the macro ignored the consolidated sheet (cws) when copying the tasks. In my new workbook, I have other worksheets I need to ignore (which don't contain tasks) and also the number of worksheets containing individuals tasks can grow each time a new project is added.
So in summary when copying and pasting tasks to the consolidated tab it needs to ignore a predefined set of worksheets and then just keep going through all of the other worksheets however many there might be.
Any help with this would be much appreciated.
Thanks