Hi All!
Here's what I'm hoping some of you wizards can help me with:
My Boss' wife has a mailing list in Excel which exceeds 500 entries. The Master list contains all the addresses. Columns 9 - 13 have designations of x, g, c, j, and p. If the person gets a mailing for Christmas, an "x" is placed in the x column. If they are part of the garden club, a "g" is placed in the g column, and so on. Each category has its own, separate worksheet (in the same workbook) to generate mail merge invitations, etc. A person can be on one or all of the mailing lists.
She regularly brings me updates. Last spring I semi-automated the address updating process by pasting a very simple "='Wellford Addresses-ALL, MASTER'!E10", etc. This process requires babysitting every time a new entry is made or one deleted, though. And further, she substantially overestimated my skills and now has me doing the mailing list for a non-profit board of which she is a member! This non-profit list has even more columns and year numbers (i.e. 13, 14, 15) are entered in the column instead of letters depending on which year the person donated, or attended or so on.
I am wading into macros for this. Since July I have been studying via Mr. Jelen's book "VBA and Macros for Microsoft Office Excel 2007" but not written any code until this month. I modified some code found on this site, but it isn't doing anything at all and I'm nearing a deadline. I am actually writing in Excel 2010 on Windows 7, but had the book from a class I took 5 years ago.
It seems like I saw a way somewhere on here to link or paste a partial spreadsheet example. If you know of a way I can do that (while changing names and contact info on sheet) please advise and I will do so. The first row contains headers. I'm trying to find a way to link active data for you to see...
Here's what I have. Apologies if it's completely off track. It does seem very long.
Here's what I'm hoping some of you wizards can help me with:
My Boss' wife has a mailing list in Excel which exceeds 500 entries. The Master list contains all the addresses. Columns 9 - 13 have designations of x, g, c, j, and p. If the person gets a mailing for Christmas, an "x" is placed in the x column. If they are part of the garden club, a "g" is placed in the g column, and so on. Each category has its own, separate worksheet (in the same workbook) to generate mail merge invitations, etc. A person can be on one or all of the mailing lists.
She regularly brings me updates. Last spring I semi-automated the address updating process by pasting a very simple "='Wellford Addresses-ALL, MASTER'!E10", etc. This process requires babysitting every time a new entry is made or one deleted, though. And further, she substantially overestimated my skills and now has me doing the mailing list for a non-profit board of which she is a member! This non-profit list has even more columns and year numbers (i.e. 13, 14, 15) are entered in the column instead of letters depending on which year the person donated, or attended or so on.
I am wading into macros for this. Since July I have been studying via Mr. Jelen's book "VBA and Macros for Microsoft Office Excel 2007" but not written any code until this month. I modified some code found on this site, but it isn't doing anything at all and I'm nearing a deadline. I am actually writing in Excel 2010 on Windows 7, but had the book from a class I took 5 years ago.
It seems like I saw a way somewhere on here to link or paste a partial spreadsheet example. If you know of a way I can do that (while changing names and contact info on sheet) please advise and I will do so. The first row contains headers. I'm trying to find a way to link active data for you to see...
Here's what I have. Apologies if it's completely off track. It does seem very long.
Rich (BB code):
Option Explicit
Sub DisributeRowsArrays()
' CGutz November 2015
' http://www.mrexcel.com/forum/excel-...s-move-rows-another-sheet-based-criteria.html
Dim wAM As Worksheet, wX As Worksheet, wG As Worksheet, wC As Worksheet, wJ As Worksheet, wP As Worksheet
Dim am As Variant, x As Variant, g As Variant, c As Variant, j As Variant, p As Variant
Dim i As Long, lr As Long, amam As Long, xx As Long, gg As Long, cc As Long, jj As Long, pp As Long
Dim n As Long, nr As Long
Set wAM = Worksheets("Wellford Addresses-ALL, MASTER")
Set wX = Worksheets("X-Wellford Addresses")
Set wG = Worksheets("G-Wellford Addresses")
Set wC = Worksheets("C-Wellford Addresses")
Set wJ = Worksheets("J-Wellford Addresses")
Set wP = Worksheets("P-Wellford Addresses")
If wAM.FilterMode Then wAM.ShowAllData
am = wAM.Range("A1").CurrentRegion.Resize(, 13)
n = Application.CountIf(wAM.Columns(9), "x")
ReDim x(1 To n, 1 To 13)
n = Application.CountIf(wAM.Columns(10), "g")
ReDim g(1 To n, 1 To 13)
n = Application.CountIf(wAM.Columns(11), "c")
ReDim c(1 To n, 1 To 13)
n = Application.CountIf(wAM.Columns(12), "j")
ReDim j(1 To n, 1 To 13)
n = Application.CountIf(wAM.Columns(13), "p")
ReDim p(1 To n, 1 To 13)
For i = 1 To UBound(am, 1)
If am(i, 9) = "x" Then
xx = xx + 1
x(xx, 1) = am(i, 1)
x(xx, 2) = am(i, 2)
x(xx, 3) = am(i, 3)
x(xx, 4) = am(i, 4)
x(xx, 5) = am(i, 5)
x(xx, 6) = am(i, 6)
x(xx, 7) = am(i, 7)
x(xx, 8) = am(i, 8)
x(xx, 9) = am(i, 9)
x(xx, 10) = am(i, 10)
x(xx, 11) = am(i, 11)
x(xx, 12) = am(i, 12)
x(xx, 13) = am(i, 13)
ElseIf am(i, 10) = "g" Then
gg = gg + 1
g(gg, 1) = am(i, 1)
g(gg, 2) = am(i, 2)
g(gg, 3) = am(i, 3)
g(gg, 4) = am(i, 4)
g(gg, 5) = am(i, 5)
g(gg, 6) = am(i, 6)
g(gg, 7) = am(i, 7)
g(gg, 8) = am(i, 8)
g(gg, 9) = am(i, 9)
g(gg, 10) = am(i, 10)
g(gg, 11) = am(i, 11)
g(gg, 12) = am(i, 12)
g(gg, 13) = am(i, 13)
ElseIf am(i, 11) = "c" Then
cc = cc + 1
c(cc, 1) = am(i, 1)
c(cc, 2) = am(i, 2)
c(cc, 3) = am(i, 3)
c(cc, 4) = am(i, 4)
c(cc, 5) = am(i, 5)
c(cc, 6) = am(i, 6)
c(cc, 7) = am(i, 7)
c(cc, 8) = am(i, 8)
c(cc, 9) = am(i, 9)
c(cc, 10) = am(i, 10)
c(cc, 11) = am(i, 11)
c(cc, 12) = am(i, 12)
c(cc, 13) = am(i, 13)
ElseIf am(i, 12) = "j" Then
jj = jj + 1
j(jj, 1) = am(i, 1)
j(jj, 2) = am(i, 2)
j(jj, 3) = am(i, 3)
j(jj, 4) = am(i, 4)
j(jj, 5) = am(i, 5)
j(jj, 6) = am(i, 6)
j(jj, 7) = am(i, 7)
j(jj, 8) = am(i, 8)
j(jj, 9) = am(i, 9)
j(jj, 10) = am(i, 10)
j(jj, 11) = am(i, 11)
j(jj, 12) = am(i, 12)
j(jj, 13) = am(i, 13)
ElseIf am(i, 13) = "p" Then
pp = pp + 1
p(pp, 1) = am(i, 1)
p(pp, 2) = am(i, 2)
p(pp, 3) = am(i, 3)
p(pp, 4) = am(i, 4)
p(pp, 5) = am(i, 5)
p(pp, 6) = am(i, 6)
p(pp, 7) = am(i, 7)
p(pp, 8) = am(i, 8)
p(pp, 9) = am(i, 9)
p(pp, 10) = am(i, 10)
p(pp, 11) = am(i, 11)
p(pp, 12) = am(i, 12)
p(pp, 13) = am(i, 13)
End If
Next i
nr = wX.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wX.Range("A" & nr).Resize(UBound(x, 1), 13) = x
nr = wG.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wG.Range("A" & nr).Resize(UBound(g, 1), 13) = g
nr = wC.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wC.Range("A" & nr).Resize(UBound(c, 1), 13) = c
nr = wJ.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wJ.Range("A" & nr).Resize(UBound(j, 1), 13) = j
nr = wP.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wP.Range("A" & nr).Resize(UBound(p, 1), 13) = p
If wAM.FilterMode Then wAM.ShowAllData
End Sub
Last edited by a moderator:
