Solved: Create a email send list

[NewDaddy]

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

[NewDaddy]

Sorry all

That didn't work very well.

Please see attached file if need.

Cheers
Jay

[NewDaddy]

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