Yet another COPY Row if Cell = Thread

Simia · May 21, 2015

Hi Everyone,

I have been lurking the forums for a few days now and have tried various snippets but coding just isn't something that comes to me easily. have tried various snippets from here and other sources, with and without edits, but trying to figure out where some of this stuff should be going in the VBA editor is sending me batty.

My wife helped me with an IF query which kind of did the job but also left lots of "FALSE" statements and "0" etc which did entirely fit my purpose.

So here is my scenario.

I have a large Spreadsheet for volunteer management.

There are close to 1000 rows and approximately 34 odd columns of data.

I have a number of sheets the main one being "Volunteer_Master". This is the sheet where I first enter all the data and create a volunteer profile. The information on it updates regularly.

We have various columns for different activities the volunteer participate in. ie Columns K L M N have the headings Activity1, Activity2, Activity3, Activity4

Each volunteer has a "1" placed in the column of the corresponding activity/activities they participate in

There are also corresponding Worksheets Activity1, Activity2, Activity3, Activity4.

What I would like to do is automate the duplication and updating of date from the "Volunteer_Master" to the corresponding activity sheets.

Here is a copy of my sample data from Volunteer_Master. As you will see some volunteers may undertake multiple activity types so therefore need to appear in multiple locations.

SURNAME FIRST NAME(S) STREET ADDRESS SUBURB STATE PCODE RESIDENTIAL (if diff) HOME PH MOBILE EMAIL Activity1 Activity2 Activity3 Activity4
Volunteris Jim Cheer Tree Laneway NSW 5515 123456 98765 jvolun@volun.com 1 1
Helperism Jane PO BOX 111 Mysteryland WA 4458 77 Beach rd 56788 43210 1
laptopimus Primus 33 Hilltop Hoodsville NSW 3321 231879 876543 laptopimus@prime.com 1
sample data1 55datamus excelville SA 2222 876565 11128889 1 1

Is there anything you can do to help me achieve this? My wife suggests I need a macro but says it is beyond her abilitie, and it is certainly beyond my without some careful guidance.

Simia · May 21, 2015

Just as a quick reference I have already looked at and tried to work through the following previous list of threads over the last few days.

http://www.mrexcel.com/forum/excel-questions/479185-copy-row-another-sheet-if-cell-contains-0-a.html

http://www.mrexcel.com/forum/excel-...copy-paste-whole-line-into-another-sheet.html

http://www.mrexcel.com/forum/excel-questions/343937-pulling-entire-row-if-cell-contains-x.html

Drrellik · May 21, 2015

if you are just building this and trying to populate the activity sheets 1,2,3,4... and I realize you have 1000's of volunteers. nothing is going to work first time because of all the variables.

I like to sort data based on a "key" meaning sort your master sheet by Activity1, then 2, then 3, then 4 and you can cut and paste the data from master to other sheets in large blocks without empty spaces. and then you can resort based on last names for quick reference or what every you need to.

However I think you should look into a Pivot Table or even and advanced filter.

DR

Simia · May 21, 2015

Drrellik said:
if you are just building this and trying to populate the activity sheets 1,2,3,4... and I realize you have 1000's of volunteers. nothing is going to work first time because of all the variables.

Hi,

Could you clarify what variables? I would have thought that each column is a single scenario with single variable? Perhaps it is my very limited understanding of the matter but I would have thought each sheet could of had its own macro and when you click to open that sheet it activates the macro that searches the related column on Volunteer_Master and refreshes the relevant sheet.

I like to sort data based on a "key" meaning sort your master sheet by Activity1, then 2, then 3, then 4 and you can cut and paste the data from master to other sheets in large blocks without empty spaces. and then you can resort based on last names for quick reference or what every you need to.

I hear what you are saying about indexing I had thought about this when thing of moving it to Access but being a volunteer organisation the idea of moving to Access scared a lot of people. Believe me I tried very hard on this.

However I think you should look into a Pivot Table or even and advanced filter.

I have never had any success with either of these, again it comes down to very limited Excel experience, but I particularly don't understand pivot tables. I tried with them yesterday and they ended up a complete mess.

Michael M · May 21, 2015

A couple of questions
1. Can you either use the HTML Maker in my tag to paste a sample of data here, or upload the worksheet to dropbox so we an work with it...it's a bit onerous looking at a screenshot then having to retype all the data
2. Do you want only columns I to J of the master sheet copied to each activity, or the entire row ?

Simia · May 22, 2015

Michael M said:
A couple of questions
1. Can you either use the HTML Maker in my tag to paste a sample of data here, or upload the worksheet to dropbox so we an work with it...it's a bit onerous looking at a screenshot then having to retype all the data
2. Do you want only columns I to J of the master sheet copied to each activity, or the entire row ?

Thanks Michael. Im just trying to get the HTML maker to work in my Excel (2013) and then will get it posted...

Simia · May 22, 2015

Michael M said:
A couple of questions
1. Can you either use the HTML Maker in my tag to paste a sample of data here, or upload the worksheet to dropbox so we an work with it...it's a bit onerous looking at a screenshot then having to retype all the data

Here it be

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>SURNAME</th><th>FIRST NAME(S)</th><th>STREET ADDRESS</th><th>SUBURB</th><th>STATE</th><th>PCODE</th><th>RESIDENTIAL (if diff)</th><th>HOME PH</th><th>MOBILE</th><th>EMAIL</th><th>Activity1</th><th>Activity2</th><th>Activity3</th><th>Activity4</th><th>ACT4_Location</th><th>Details Verified</th><th>district</th><th>noMail</th><th>Comment</th></tr>
<tr><td>Volunteris</td><td>Jim</td><td>Cheer Tree</td><td>Laneway</td><td>NSW</td><td>5515</td><td> </td><td>123456</td><td>98765</td><td>jvolun@volun.com</td><td>1</td><td> </td><td>1</td><td> </td><td> </td><td>1</td><td>in</td><td>n</td><td> </td></tr>
<tr><td>Helperism</td><td>Jane</td><td>PO BOX 111</td><td>Mysteryland</td><td>WA</td><td>4458</td><td>77 Beach rd</td><td>56788</td><td>43210</td><td> </td><td> </td><td>1</td><td> </td><td> </td><td> </td><td> </td><td>out</td><td> </td><td> </td></tr>
<tr><td>laptopimus</td><td>Primus</td><td>33 Hilltop</td><td>Hoodsville</td><td>NSW</td><td>3321</td><td> </td><td>231879</td><td>876543</td><td>laptopimus@prime.com</td><td> </td><td> </td><td> </td><td>1</td><td>Sunbury</td><td> </td><td>in</td><td> </td><td> </td></tr>
<tr><td>sample</td><td>data1</td><td>55datamus</td><td>excelville</td><td>SA</td><td>2222</td><td> </td><td>876565</td><td>11128889</td><td> </td><td> </td><td>1</td><td>1</td><td> </td><td> </td><td> </td><td>out</td><td> </td><td></td></tr>
</table>

2. Do you want only columns I to J of the master sheet copied to each activity, or the entire row ?

No, it would need to be from A-J. The Activity(X) Worksheets are provided to the various activity coordinators for them to generate mailing, emailing and contact lists, etc etc. So full volunteer details are required. It may be easier to make it a full row copy so that if any additional fields are added later this will come across.

What I would like to do is automate the duplication and updating of data from the "Volunteer_Master" to the corresponding activity sheets. Full automating would be great but if it meant having to press a button as a "trigger" I could certainly live with that. Or when you click to open a particular activity sheet it activates the script that searches the related column on Volunteer_Master and refreshes the relevant activity sheet.

Michael M · May 22, 2015

Make the Volunteer Master the activesheet, then run this code.
I have assumed that each Activity sheet has a Header row

Code:

Sub MM1()
Dim ws As Worksheet, lr As Long, lr2 As Long, r As Long
Application.ScreenUpdating = False
lr = Sheets("Volunteer_Master").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
    If ws.Name <> "Volunteer_Master" Then
        ws.Activate
        lr2 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Rows("2:" & lr2).Delete
    End If
Next ws
Sheets("Volunteer_Master").Activate
For Each c In Range("K2:N" & lr)
    If c.Value = 1 Then
        lr2 = Sheets(Cells(1, c.Column).Value).Cells(Rows.Count, "A").End(xlUp).Row
        Rows(c.Row).Copy Sheets(Cells(1, c.Column).Value).Range("A" & lr2 + 1)
        lr2 = Sheets(Cells(1, c.Column).Value).Cells(Rows.Count, "A").End(xlUp).Row
    End If
Next c
Application.ScreenUpdating = True
End Sub

Michael M · May 22, 2015

Disregard last post, it deletes the header rows on the activity sheets.
Use Instead

Code:

Sub MM1()
Dim ws As Worksheet, lr As Long, lr2 As Long, r As Long
Application.ScreenUpdating = False
lr = Sheets("Volunteer_Master").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
    If ws.Name <> "Volunteer_Master" Then
        ws.Activate
        lr2 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        If lr2 > 1 Then Rows("2:" & lr2).Delete
    End If
Next ws
Sheets("Volunteer_Master").Activate
For Each c In Range("K2:N" & lr)
    If c.Value = 1 Then
        lr2 = Sheets(Cells(1, c.Column).Value).Cells(Rows.Count, "A").End(xlUp).Row
        Rows(c.Row).Copy Sheets(Cells(1, c.Column).Value).Range("A" & lr2 + 1)
        lr2 = Sheets(Cells(1, c.Column).Value).Cells(Rows.Count, "A").End(xlUp).Row
    End If
Next c
Application.ScreenUpdating = True
End Sub
[

Simia · May 22, 2015

Michael M said:

Make the Volunteer Master the activesheet, then run this code.
I have assumed that each Activity sheet has a Header row

Code:

Sub MM1()
Dim ws As Worksheet, lr As Long, lr2 As Long, r As Long
Application.ScreenUpdating = False
lr = Sheets("Volunteer_Master").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
    If ws.Name <> "Volunteer_Master" Then
        ws.Activate
        lr2 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Rows("2:" & lr2).Delete
    End If
Next ws
Sheets("Volunteer_Master").Activate
For Each c In Range("K2:N" & lr)
    If c.Value = 1 Then
        lr2 = Sheets(Cells(1, c.Column).Value).Cells(Rows.Count, "A").End(xlUp).Row
        Rows(c.Row).Copy Sheets(Cells(1, c.Column).Value).Range("A" & lr2 + 1)
        lr2 = Sheets(Cells(1, c.Column).Value).Cells(Rows.Count, "A").End(xlUp).Row
    End If
Next c
Application.ScreenUpdating = True
End Sub

Hi Michael So I did the "AltF11" to open up the editor. Double clicked on Volunteer_Master on the left and then pasted the code snippet on the right. Closed it and then ran the macro.

All the data disappeared from Volunteer_Master and nothing was placed on the Activity Sheets.

Thoughts?

Yet another COPY Row if Cell = Thread

Simia

New Member

Excel Facts

Simia

New Member

Drrellik

Well-known Member

Simia

New Member

Michael M

Well-known Member

Simia

New Member

Simia

New Member

Michael M

Well-known Member

Michael M

Well-known Member

Simia

New Member

Forum statistics

Share this page

Yet another COPY Row if Cell = Thread

New Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock