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

G

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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:
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
L. Howard said:
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
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
259
Alex Blakenburg
A
K
  • Question
conditional formatting question
Replies
2
Views
149
kerm007
K
P
  • Question
Data not copying fully into ListBox2
Replies
0
Views
83
Patriot2879
P
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
191
bobaol
B
R
  • Solved
I need to find a row based on multiple criteria, with a catch.
2
Replies
13
Views
458
rcrumbliss
R

Forum statistics

Threads
1,214,540
Messages
6,120,107
Members
448,945
Latest member
Vmanchoppy

Share this page

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
Back
Top