how to copy rows (& auto update) to another (existing) worksheet based on criteria

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
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.

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:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
SURNAMENAMESPOUSEADDRESSADDRESS 2NOTESPHONEEMAILXGCJP
AbAliceBendview Rd.
Charleston, WV 22222
H 304-555-1212gc
AgTashaQuarry Ridge
Charleston, WV 22222
j
AlCynthiaOrchard St.
Belmont, MA 00444
AldMr. & Mrs.Hamilton Avenue
Clifton Forge, VA 24224
x
AleBettyFern Road
Charleston, WV 22222
H 304-555-1211jp
AlexaMr. & Mrs. R.BettyFern Road
Charleston, WV 22222
H 304-555- 3441p
AlhIngridHeights Rd.
Charleston, WV 22222
cjp

<tbody>
</tbody>



























OK, here's some sample data. This is the Master list which contains everybody.
Thank you in advance. :)
 
Last edited:

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
SURNAMENAMESPOUSEADDRESSADDRESS 2NOTESPHONEEMAILXGCJP
AbAliceBendview Rd.
Charleston, WV 22222
H 304-555-1212gc
AgTashaQuarry Ridge
Charleston, WV 22222
j
AlCynthiaOrchard St.
Belmont, MA 00444
AldMr. & Mrs.Hamilton Avenue
Clifton Forge, VA 24224
x
AleBettyFern Road
Charleston, WV 22222
H 304-555-1211jp
AlexaMr. & Mrs. R.BettyFern Road
Charleston, WV 22222
H 304-555- 3441p
AlhIngridHeights Rd.
Charleston, WV 22222
cjp

<tbody>
</tbody>


OK, here's some sample data from the Master List containing all addresses.
Thank you in advance. :)
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi GutzyRose,

The sample data you posted is a tiny bit of a much greater persons list, I presume.

And you want to click a button (or run a code) that shuffles the names to the correct worksheet if that name is "coded" with either X G C J P, on their row, is that correct?

Where, Alice (and row info) would go to sheets G-Wellford Addresses & C-Wellford Addresses per your example.

And how many names are we talking about, 50 or so or 2500 or the whole city?

It would seem you would want to be able to choose single or multiple mailing designators, for example, in Dec you would want only Christmas mailings, but not the Garden Club (they are probably in Florida anyway that time of year).

Howard

You can use one of the link utilities to post a link to a sensitized workbook here in this forum. I use Drop Box, but there are others.
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35

ADVERTISEMENT

The Wellford book is over 500 addresses. The non-profit is about 1000. They want to be able to change any data (address, phone, what "group" they belong to, or if they are on the list at all) on only the master sheet and have it automatically change on any other sheet. And it appears you have a good handle on what needs done "Where, Alice (and row info) would go to sheets G-Wellford Addresses & C-Wellford Addresses per your example."exactly that. Except it needs to copy the information and leave the Master list intact. The whole thing would be easier (for me) in Access, because all you do is set up and a run a report. But neither she or the non-profit use Access, so Excel it is (-:
Your help is greatly appreciated as I have only the vaguest grasp of true programming.
CGutz
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi CGutz,

Can you "dummy down" the names & address' on the Wellford book and post a LINK to it here? With a Drop Box like utility.

I think one easy way to dummy the sensitive info on text data (we are not doing math calc's here) is to do several sheet wide Find/Replace, like find 0 (zero) replace with 1, find 5 replace with 99, and replace all "a" & "A" with "x" same with the "e" & "E". You still have text and numbers on the sheet and that is what we are moving/copying to other sheets etc.

Is the non-profit book in the same format as the Wellford? If so Wellford stuff should work for non profit also.

Here is a quickie start, however a bit slow, workbook using you posted data.

https://www.dropbox.com/s/cv5pgcvlhexa5yg/GutzyRose Mail List.xlsm?dl=0

Click the Do button, check the sheets.

Howard
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35

ADVERTISEMENT

Hi CGutz,

Can you "dummy down" the names & address' on the Wellford book and post a LINK to it here? With a Drop Box like utility.

I think one easy way to dummy the sensitive info on text data (we are not doing math calc's here) is to do several sheet wide Find/Replace, like find 0 (zero) replace with 1, find 5 replace with 99, and replace all "a" & "A" with "x" same with the "e" & "E". You still have text and numbers on the sheet and that is what we are moving/copying to other sheets etc.

Is the non-profit book in the same format as the Wellford? If so Wellford stuff should work for non profit also.

Here is a quickie start, however a bit slow, workbook using you posted data.

https://www.dropbox.com/s/cv5pgcvlhexa5yg/GutzyRose Mail List.xlsm?dl=0

Click the Do button, check the sheets.

Howard

Working on it. We don't use Drop Box here, will try to link page. She gives me these projects in addition to my regular job - hopefully I can have it up before lunch today. The "Do" button didn't do anything , but I need to try a couple other things yet... Thank you!
CGutz
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
And yes, the non-profit list is the same idea with 2 pertinent differences: There are 10 category columns instead of 5. And the codes in the columns are numbers, not letters. (I read one of these posts which stated the macro has to be written differently to accommodate numbers vs. letters). Those numbers will change as years go by.
Back soon (-:
CGutz
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
OK, I hope this works. When I deleted a couple of tabs, the code I had written got deleted with them. It is saved in my non-dummied down version and I doubt you need it anyway. Here is the link to the file:
https://www.dropbox.com/s/x1k86dht1...ellford address book -november 2015.xlsm?dl=0

As long as the code works regardless of whether the column contains numbers or letters, then it'd help me to learn by applying the solution to the non-profit mailing list for myself. After my brief acquaintance with Drop-Box, I'm not seeing where the Developer (or any) ribbon is so that I can go in and see the code though. I want to be able to compare what I did that didn't work to what you've changed that makes it work.

If you're anywhere near Charleston, WV I owe you a martini or two.
CGutz
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
Howard, Now I see! :biggrin:
Your code is SO-O-O-O much simpler than the path I was on! Boss John is here now, but I'll play with this more today and see if I can tweak it. Or if I even need to tweak it.
Thank You, Thank You, Thank You!!!!
Do you even like martinis?
CGutz
 

Watch MrExcel Video

Forum statistics

Threads
1,129,700
Messages
5,637,887
Members
416,988
Latest member
Ahmed_Yehia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top