Solved: Create a email send list

Hi all

I need some help please. I am not sure how to do this at all. I have the following sheets: Catalogue List and Sheet B.

In the Catalogue List sheet I have centre numbers in column A and names of people in columns H, I, J & K which forms a distribution list of people wanting reports for that centre.

I would like to build a template to use in emailing out reports. What I need is to create Sheet B with a list of names in column A and all the centre numbers for that person starting in column B, onwards.

That is to ‘populate’ column A of Sheet B with all the names that appear in columns H to K from the Catalogue List sheet and bring in all their corresponding centre numbers in column B onwards, i.e. centre numbers would start in column B and end wherever rather than a string of centre numbers in column B.

I am not sure if the following table will work to illustrate what I am looking for but hear goes, oh and I have also put in an example of the Catalogue List.

If it doesn’t I hope I have explained clearly enough.

Edit by mdmackillop: see attachment in next post


As you may be able to see a person can appear in any column but only once for each centre (row).

As ever your help is greatly appreciated.

Cheers
Jay

Sorry all

That didn't work very well.

Please see attached file if need.

Cheers
Jay

Hi All

In the meantime I have found a way round my problem. I create (using pivot tables) a list of budget manager names with corresponding cost centres. From there I run the routine I wrote which puts it into the required format.

I thought I better post to give an update but also to show what I have come up with, any comments/improvements would be appreciated.

Also I am currently testing a routine I have just finished writing which then takes the distribution list and creates a report file for each budget manager. I can post this later if people are interested.

[vba]
Sub CompileDistList()
BudgMgrSht = ActiveSheet.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Dist List").Delete
On Error GoTo 0
Sheets.Add.Name = "Dist List"
DList = ActiveSheet.Name
With Range("A1:B1")
.Value = Array("Distribute to", "Cost Centres")
.Font.Bold = True
.Font.Size = 12
End With
Columns("A:A").ColumnWidth = 25
Sheets(BudgMgrSht).Activate
Cells.Find("Budget Manager", LookIn:=xlValues, MatchCase:=False).Select
y = 1
j = 1
Do Until ActiveCell.Offset(x, 0).Value = ""
If ActiveCell.Offset(x, 0).Value = ActiveCell.Offset(y, 0).Value Then
j = j + 1
CCentre = ActiveCell.Offset(y, 1).Value
Sheets(DList).Activate
ActiveCell.Offset(BMgrCount, j).Value = CCentre
Else
j = 1
BMgrCount = BMgrCount + 1
BMgr = ActiveCell.Offset(y, 0).Value
CCentre = ActiveCell.Offset(y, 1).Value
Sheets(DList).Activate
Range(ActiveCell.Offset(BMgrCount, 0), ActiveCell.Offset(BMgrCount, 1)).Value = _
Array(BMgr, CCentre)
End If
x = x + 1
y = y + 1
BMgr = ""
CCentre = ""
Sheets(BudgMgrSht).Activate
Loop
End Sub
[/vba]

Cheers