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:

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
Well, I'll be :LOL:
I just did a copy and paste, thinking I'd at least have a picture of the blue button. It brought functionality with it, lol.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi Howard,
We're not done yet )-:
I've been plugging the code into my non-profit sheet. It runs well. But The Blue Box to run the macro does not appear, nor do the icons to clear the data.
Otherwise plugging away at it...
Christine

Right-click the blue shape, > Assign_Macro > Copy_Non_Profit >OK.

Then the same for the clear on the !ALL CP! sheet assign the Clear_ALL_CP_Sheet to the red icon.

For each of the other sheets assign the ClearThisSheetOnly to each red icon on each sheet.

Clear codes are not needed for the main transfer code to work.

Howard
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Well, I'll be :LOL:
I just did a copy and paste, thinking I'd at least have a picture of the blue button. It brought functionality with it, lol.

I wonder if Drop Box dumps the buttons, and shapes sometimes. I seem to have had that problem for awhile, but now seems ok. I down load back to me the links I post and this one worked as intended on my end.

So I guess you are still testing, and I am standing by if needed.

Howard
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Well, I'll be :LOL:
I just did a copy and paste, thinking I'd at least have a picture of the blue button. It brought functionality with it, lol.

Just to be sure, the blue rectangle and the red circle/slash are Shapes from the Insert Tab > Shapes. Not a button from Developer > Form Controls.

Howard
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35

ADVERTISEMENT

That did it!
Learning and working great so far. I am in my actual (saved separately to be safe) non-profit workbook doing this, and it is working smoothly. I do still have to enter some test data though. And for some reason the "clear" button worked fine on all worksheets until the very last "Public". In that 1 case it cleared my headers too, not just the transferred data.
Christine
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
That did it!
Learning and working great so far. I am in my actual (saved separately to be safe) non-profit workbook doing this, and it is working smoothly. I do still have to enter some test data though. And for some reason the "clear" button worked fine on all worksheets until the very last "Public". In that 1 case it cleared my headers too, not just the transferred data.
Christine

Look closely at Public vs. the other sheets. Maybe on one of the other sheets do a sheet > Copy and paste to sheet Public. Then try the clear.

Howard
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35

ADVERTISEMENT

That worked. I also put some random dates in, top to bottom and they all generated, so I am happy-happy-happy! I have to do some updates, but otherwise it looks like I'll be able to hand it off to the non-profit Monday. Your help has been invaluable. So happy to clear this off my to-do list!

Christine
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
That worked. I also put some random dates in, top to bottom and they all generated, so I am happy-happy-happy! I have to do some updates, but otherwise it looks like I'll be able to hand it off to the non-profit Monday. Your help has been invaluable. So happy to clear this off my to-do list!

Christine

Okay, great.

I do worry about the size of the "old" non-profit. It is around 13MB, whereas the re-make I sent was about 241KB, without any color formatting. Maybe not a big deal, but the 13MB was pretty sluggish and did freeze up on me a couple times.

Double Beefeaters, up, with two olives, please.

Regards,
Howard
 

GutzyRose

New Member
Joined
Nov 25, 2015
Messages
35
Okay, great.

I do worry about the size of the "old" non-profit. It is around 13MB, whereas the re-make I sent was about 241KB, without any color formatting. Maybe not a big deal, but the 13MB was pretty sluggish and did freeze up on me a couple times.

Double Beefeaters, up, with two olives, please.

Regards,
Howard

Oh, a gin man eh? Are you anywhere Charleston, WV? I really do owe you. You have been a lifesaver.

And PS: I've run that code at least a dozen times now with no noticeable slowing. I am expected to email the finished workbook though and may just burn it onto a disc or thumb drive to be safe. I have to walk them through using it anyway. Drop Box did give me a notice, the very first link I posted to the first book, about it being full. I took it as them selling more space. And the message never repeated so...

Happy Friday!!! Where to for frosty beverages???
Christine
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Oh, a gin man eh? Are you anywhere Charleston, WV? I really do owe you. You have been a lifesaver.


Happy Friday!!! Where to for frosty beverages???
Christine

No, I'm an Olive man. The gin just taste good with them!

I'm in Oregon, but thanks for the offer.

Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,130,088
Messages
5,640,041
Members
417,124
Latest member
Herostrata

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