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

[GutzyRose]

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.

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

 

[L. Howard]

Hi CGutz,

Don't fall in love with that dreadfully slow code. It is only working on a dozen or so names/address'.

I will look at the dummie book and see whatwe can do.

Howard

 

[GutzyRose]

OK. I haven't had the time to actually try it on my full workbook. But I did look at the code and notice, the way your ranges are set won't work with the non-profit because those codes are year numbers which aren't specific to the receiving worksheet. The column title (DN, or ATT, IVT etc.) is. (i.e. 12 for the year they donated or attended the party or were sent an invite, etc)
Anyway. I am going to go over Chapter 5 in Jelen's VBA book again. (Hopefully today. Boss John is insisting on other priorities.) It covers loops but also copying rows and criteria. Maybe I can find something as well.
It's nice to have 2 minds on this.
Christine

 

[L. Howard]

Hi Christine,

A couple of things with the Practice workbook.

First Merged cells have to go away. They cause numerous problems in data and when using code. There are other ways to accommodate the data without merging cells.

Second, I see some name "Blocks" are one row (awaiting additional info??) and most are two row and a few are three or four row dedicated to a person with some unique address info.

We must decide on a consistent number of rows for each and every name block. I would suggest three rows. Then if you have additional "need to know" info about that name, address or special info, then in the Notes column/s do an Insert Comment, and put the info in the comment. The comment will copy and paste to the secondary sheets. And with a three row base for each name there would be three "Notes" cells per name if you ever needed that many.

I will proceed with these assumption unless you have objections.

I would not use the Select Case code in my example on you full workbook, it will surely fail because of the items I mention here about the data structure.

Howard

 

[GutzyRose]

Yes please. I have no problems with your assumptions.
In my defense, I did not create this workbook but inherited the format from whoever Boss' Wife (BW going forward) initially gave it to, lol. Once I put it in Drop Box the separations appeared and I knew that would be trouble. Address blocks traditionally are separate cells for the street line, then city state at the very least. I've already rebuilt the non-profit sheet in that manner. I had planned on doing it 2nd. But maybe I can quickly restructure it and start there instead.....
Proceed as you wish (-:

 

[GutzyRose]

https://www.dropbox.com/s/vgvudqdynsmpxa6/CP Drop Box List.xlsx?dl=0

Hi Howard,
I've scrambled the names and addresses on the non-profit's spreadsheet. Their addresses are formatted correctly. And frankly they are the more pressing issue. I will later apply what we do here to the BW's mailing list as time allows. When I substituted the phone and address numbers it scrambled the year numbers in the right hand columns. But you get the idea - a year will be entered. The column corresponds to the worksheet. I can rename things as needed later, on my own. I'm here another 45 minutes, then bright and early tomorrow.
CGutz

 

[L. Howard]

I see no column for "a year will be added".

Post here the headers as they will appear on the actual real workbook from column A to >>>>? It is important the columns are labeled correctly.

You can click the Cloud icon in the header here, copy the headers in it.

So the name block seems to be a single row and the number in column N to W determines the sheet that row goes to, and may go to multiple sheets if multiple numbers in the N--W columns?



Howard

 

[L. Howard]

One more thing please.

Can you give me an explanation on the numbers in the N to W columns.

Take row 15, with Mr. Harry. He has 24 in the PVT INV and 15 in the FCPM. Does his data row just copy to each of those sheets? Why the number instead of say an "x"?

Howard

 

[GutzyRose]

Good morning Howard (-:
I see I wasn't very clear. I have to learn not to rush these things.
First things first - I've attached a new dropbox file for CP non-profit with unaltered numbers where it matters.
https://www.dropbox.com/s/vgvudqdynsmpxa6/CP Drop Box List.xlsx?dl=0

Columns N - W are year numbers. The categories at the top correspond to the individual worksheet. The current President of the non-profit Board wants tabs (and columns) with a note whether they were active in the category in a given year. So row 15, Mr. Harry was PTY IVT (Part Invitation) in 14 and a FCP M (Friend of CP Member) in 15. So his data needs to copy to the PTY IVT sheet and the FCP M sheet.

"So the name block seems to be a single row and the number in column N to W determines the sheet that row goes to, and may go to multiple sheets if multiple numbers in the N--W columns? " No. The number does not determine which sheet. The column header determines which sheet.

A-C are blank because their accounting (or someone) makes notes in them. I have left these in for whatever their purposes are (not explained to me and the reasons probably change). This group is run by a club. They want something a complete Excel novice can do since the club changes officers and the people who do the mailings, etc. frequently. No filters a newbie has to apply or sort, he / she just clicks a button and presto magico it all updates. They can look at any given category and see who participated in what time frame.

I'll take a look at the cloud, but I've been careful to make sure the headers are correct since they are the critical sort tool in this instance.
Christine

 

[GutzyRose]

Oh, and the Master sheet , from which all data generates, is ALL CP. (And not that it matters, but typo above PTY IVT is Party Invite)

 

[GutzyRose]

Column"H" header should say "Email"
updated link:
https://www.dropbox.com/s/vgvudqdynsmpxa6/CP Drop Box List.xlsx?dl=0