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.

Please Login or Register  to view this content.

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

Hi Jindon,
I tried your version and received the following....
break.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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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"