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

Jay3

Board Regular
Joined
Jul 3, 2009
Messages
235
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?

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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Jay3

Board Regular
Joined
Jul 3, 2009
Messages
235
This is an individual project with the board name in B2, this one is called "Ad-hoc Tasks"

1583231383898.png


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

1583231429885.png
 

Attachments

  • 1583231312407.png
    1583231312407.png
    11.3 KB · Views: 0

Jay3

Board Regular
Joined
Jul 3, 2009
Messages
235
Actually I could just add a column for board name in the project boards that might help keep it simple.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top