Consolidating text from multiple sheets into master - can you help?

Hello all,
I have a need to create a workbook with a master sheet that consolidates text from the sheets within the workbook. There are threads that answer this question for basic sheets but mine is complicated by the need for the sheets to be calendar based.

The workbook is basically to be used as a marketing campaign consolidation and planning tool.

Any help in making this possible is appreciated!
Thanks

Thanks Sixthsense, I have attached a copy with some text in it, just 5 examples but all consolidating in the master for the corresponding dates. There will be several days in the year where the master will sow days with actions from several of the campaign sheets.

A new line would be required for each consolidated item on the same day in the master. Hope that makes more sense than my first attempt at explaining

thanks

yes the blind match can be made for all cells in all campaigns against the same cells in the master

thank you I will give it a try!

sorry sixthsense, running the macro resulted in this error message:-

Dim Ws As Worksheet, wMaster As Worksheet Dim iStCl As Integer, iEndCl As Integer Dim iStRw As Long, iEndRw As Long, i As Integer Dim rMyRng As Range, r As Range, vTemp As Variant

Here's my interpretation of your problem.

Option Explicit

Sub test()
    Dim i As Long, ws As Worksheet
    Application.ScreenUpdating = False
    With Sheets("master").Cells(1).CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 2)
            For i = 3 To 17 Step 2
                .Columns(i).ClearContents
                For Each ws In Worksheets
                    If Not ws Is .Parent Then
                        .Columns(i).Value = .Parent.Evaluate(.Columns(i).Address & _
                        "&if('" & ws.Name & "'!" & .Columns(i).Address & "<>"""",if(" & _
                        .Columns(i).Address & "<>"""",char(10),"""")&'" & _
                        ws.Name & "'!" & .Columns(i).Address & ","""")")
                    End If
                Next
            Next
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Hi Jindon,
I tried your version and received the following....
break.PNG

Here is screenshot, presume its the line in red
campaign calendar.PNG

Did you try the file I attached?

Thanks Jindon it worked, I must have made an error in running?

I note I have to run the macro after updating the campaign sheets, is that intended or should it update without having to do so?

If you want it to update altomatically then

Add following code to "master" sheet module.

Private Sub Worksheet_Activate()
    test
End Sub

So that it will update every time you select "master" sheet.

Thanks, Im not sure if my version of excel is the same or Im just doing the wrong thing, can you upload one final version for me, apologies.

See attached.

Jindon and sixthsense, thanks so much for your assistance! I wish I was so talented as a code writer!

CFWA

If that takes care of your original question, please select Thread Tools from the menu and mark this thread as SOLVED. Thanks.

campaign calendar 2014.xlsmhello again, using the workbook we have discovered the need to enhance it further. Hoping someone can assist with the following, I have attached a copy of the working document which will help explain some of the additional fuctionality:-

1. Months September - December are not consolidating in the master for any of the worksheets
2. In the master it would be useful to display * before each entry to distinguish each entry on the day
3. carry text formatting from each worksheet to master. eg if text is red in worksheet it shows as red in master
4. insert the name of the worksheet in the master for each entry so that it looks like this for example * Appeals - xyz

Originally Posted by CFWA

1. Months September - December are not consolidating in the master for any of the worksheets
2. In the master it would be useful to display * before each entry to distinguish each entry on the day
4. insert the name of the worksheet in the master for each entry so that it looks like this for example * Appeals - xyz

Change

            For i = 3 To 17 Step 2
                .Columns(i).ClearContents
                For Each ws In Worksheets
                    If Not ws Is .Parent Then
                        .Columns(i).Value = .Parent.Evaluate(.Columns(i).Address & _
                        "&if('" & ws.Name & "'!" & .Columns(i).Address & "<>"""",if(" & _
                        .Columns(i).Address & "<>"""",char(10),"""")&'" & _
                        ws.Name & "'!" & .Columns(i).Address & ","""")")
                    End If
                Next
            Next

to

            For i = 3 To 25 Step 2
                .Columns(i).ClearContents
                For Each ws In Worksheets
                    If Not ws Is .Parent Then
                        .Columns(i).Value = .Parent.Evaluate(.Columns(i).Address & _
                        "&if('" & ws.Name & "'!" & .Columns(i).Address & "<>"""",if(" & _
                        .Columns(i).Address & "<>"""",char(10),"""")&""*""&""" & ws.Name _
                        & """&'" & ws.Name & "'!" & .Columns(i).Address & ","""")")
                    End If
                Next
            Nex

Originally Posted by CFWA

3. carry text formatting from each worksheet to master. eg if text is red in worksheet it shows as red in master

Change partial cell fonts attributes are too much work.

Thanks Jindon!

Any takers of the carry text formatting from each worksheet to master. eg if text is red in worksheet it shows as red in master???

ok I have one last request

I have attached the latest version of the sheet and thanks to Jindon the other items have now been incorporated.

We have added a sheet called campaign calendar before the "master sheet". The campaign calendar will summarise the dates each campaign runs for. For example the first notation in the appeals sheet is on 17 March and the last is on 17 December.

Code is required to extract the start and finish date for each campaign worksheet and block the cells from start to finish in the campaign calendar. The cells from A3 down will list the worksheets.campaign calendar 2014.xlsm

If this is not what you wanted, post a desired "campaign calendar" sheet after the macro.

Sub EventClalendar()
    Dim ws As Worksheet, Sdate, Edate, r As Range, x, y
    With Sheets("campaign calendar")
        With .Range("a2").CurrentRegion.Offset(2)
            .ClearContents: .Interior.ColorIndex = xlNone
        End With
        For Each ws In Worksheets
            If (ws.Name <> .Name) * (ws.Name <> "master") Then
                With ws
                    With Intersect(.Rows("2:38"), .Range("c1,e1,g1,i1,k1,m1,o1,q1,s1,u1,w1,y1").EntireColumn)
                        Set r = .Find("*", , , , , 1)
                        If Not r Is Nothing Then
                            Sdate = ws.Cells(1, r.Column - 1).Value & ":" & r(1, 0).Value
                            Set r = .Find("*", .Cells(1), , , , 2)
                            Edate = ws.Cells(1, r.Column - 1).Value & ":" & r(1, 0).Value
                        End If
                    End With
                End With
                With .Range("a" & Rows.Count).End(xlUp)(2)
                    .Cells(1).Value = ws.Name
                    If Not r Is Nothing Then
                        .Cells(1).Font.Bold = True
                        x = Application.Match(Split(Sdate, ":")(0) & "*", .Parent.Rows(1), 0)
                        y = Application.Match(Split(Edate, ":")(0) & "*", .Parent.Rows(1), 0)
                        .Parent.Range(.Parent.Cells(.Row, x + Split(Sdate, ":")(1) - 1), _
                        .Parent.Cells(.Row, y + Split(Edate, ":")(1) - 1)).Interior.Color = vbRed
                    End If
                End With
            End If
        Next
    End With
End Sub

this is the result I got when running the macro..... not showing other entries....campaign calendar 2014.xlsm

Originally Posted by jindon

If this is not what you wanted, post a desired "campaign calendar" sheet after the macro.

.....................

sorry Jindon I'm not sure quite what you mean.

for example on the appeals sheet the first entry occurs on 17 march and last on 17 december, however the campaign calendar does not show this entry as it does for 65 Roses.

I was hoping for each worksheet date range to be shown in the campaign calendar.

can anyone else assist?

I have another issue, it seems some unwanted text is appearing in the master sheet.
error.PNG
When I run the macro it advises there is a run time error error and the debugger reveals the following line as being the error:

With .Offset(1).Resize(.Rows.Count - 2)

and when I try to save the following error is revealed in the debugger.

Application.Run Sheets("master").CodeName & ".worksheet_activate"