Sort out data on seperate sheet

[Guest]

This is more difficult than I thought. I have a workbook titeld Habitat. I
have a large spreadsheet with my volunteer information on it. I've created
separate sheets and I want to pull various combinations of data from the
master sheet titled Volunteers to the other sheets. The data I'm pulling
from is just based on two columns, the "Available" Column and the "Task"
column. For instance, I want to copy and paste the result for

From the Task Column = Plumbing
From the Available Column = "Sat" and "Sun"

How would I write something like this. Thank you.

 

[Otto Moehrbach]

Linda
Not sure of what you have and also not sure of what you want to do.
You have a Master sheet that has various columns but for this purpose
you want to pull data from only the "Available" and "Task" columns. I think
I have that right. Do I? What I don't know is anything at all about where
you want this data to go. You say you have "several" sheets and you want
this pulled data to go into those sheets. Where in those sheets? In what
columns? In what rows? Do those sheets have names that relate somehow to
the data that is being pulled? Please post back with more detail. HTH
Otto

 

[Guest]

Thanks Otto for replying. This is all one workbook and all my sheets in the
workbook have the same column heading. I guess what I'm trying to do is sort
out mini lists from the master list while keeping the master list in tact. I
want to automate this with a macro becasue my volunteer list changes weekly.

I only have a few columns in all my sheets, they are
"Name" "WPhone" "HPhone" "Available" "Task"
A B C D E

I want to only copy and paste to my "Plumbing Weekend" Sheet those
volunteers who have "Plumbing" in their Task column and "Sat" and "Sun" in
their "Available" column. Does that help at all. I want to keep this all in
the same workbook.

Thank you very much.

 

[Otto Moehrbach]

Linda
This little macro will do what you want. This macro assumes the
following:
The destination sheet is named "Plumbing Weekend".
The name of the source sheet doesn't matter, but it must be the active
sheet.
On both sheets, the headers are in row 1 and the data starts with Column A.
As written this macro copies only the Plumbing and pastes it into the
destination sheet. Do you need this macro to do the same thing for all the
other destination sheets? I would think so but you asked for only the
plumbing. If so, give me the sheet names as well as the corresponding
"Task" listing in the source sheet. HTH Otto
Sub CopyPlumbSatSun()
Dim RngColA As Range
Dim i As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Plumbing Weekend")
For Each i In RngColA
If (i.Offset(, 3) = "Sat" Or i.Offset(, 3) = "Sun") And _
i.Offset(, 4) = "Plumbing" Then _
i.Resize(, 5).Copy .Range("A" &
Rows.Count).End(xlUp).Offset(1)
Next i
End With
End Sub

 

[Guest]

HI Again Otto,

Thanks so very much for your help.

Because my volunteers call in daily with their schedules, I need to be able
to just update my master list and run the macro to update my tabs(sheets)
with their availability for the week.

My tabs(sheet names) are "Siding", "Roofing", "Plumbing", "Framers",
"Electrical", "Interior Finish". I want to just pull into those lists my
time slots "Available: which are "Sat" and/or "Sun". I'm sure if you can
help me with a tab or two, I'll try to finish the rest.

I'm thinking I just need to open the master list, run a macro that updates
all my sheets, is that correct?

Yes, my headers are on row 1 and data starts in column A.

You are a life saver...this will may my life so much easier as folks
availability changes daily.

Thanks so much.

Linda

 

[Otto Moehrbach]

Linda
It's much easier for me and you if I write the macro to do all the
sheets.
One thing I need to tell you and you tell me if this fits in with what
you are doing. It is much more difficult (much more code needed) for me to
write the macro to only update the sheets with the latest information. It
is much easier if I write the macro to clear (erase) each sheet, in turn,
and then copy ALL the info pertaining to that sheet. This would be a
problem for you if you are manually putting other information into each or
some of the sheets because the code might erase that. Let me know on this.
I'll assume, until I hear different from you, that it's OK to clear each
sheet.
I understand that the sheet names and the task wording are the same. Is
that correct?
Do you want the sheets sorted (simple code required)? By name only? Or by
name and then by task?
How about sorting the Volunteer sheet at the same time? Would that help
you? Sort by what?
Otto

 

[Guest]

HI Again Otto,

Yes, it is perfecty OK to delete previous, then I will only be looking at
the most current volunteer availability. I hope the code isn't too
complicated since I may need to add more sheets down the road.

I would like to keep sheet one in tact at all times, that is my master list
of "volunteers". I download that from a master list on tab 1 which I
download sometimes 2 times a day. Then from tab 1, I'd like my sheets which
are titled below to include who can work "Sat" and "Sun". Then I have
another set of Job Tabs for "Sat" only. Most of the time I just use the "Sat"
report but sometimes I need both "Sat" and "Sun" so I made both tabs for all
jobs noted below.

I certainly hope this isn't way too complicated, I thought this would be
easy, just to update the sheets from the master with a macro. I learn
something new every day!

Thank you so very much.

 

[Otto Moehrbach]

Linda

Do you mean that you have sheets with the task names and then
you have sheets with the same task names followed by "Sat"? Do you have
this for every task?

Perhaps it would be easier on both of us if you send me your
file. Then I wouldn't have to ask all these questions. If you feel that
some of the data is proprietary, just fake the data. I just need the layout
and the sheet names and the task names.

We've been talking about "Sat" and "Sun" only. Do you have a
similar task with the other days of the week? Believe me, it's much easier
to do everything you need in one shot, so tell me everything you need.

Also tell me the version of Excel you are running.

My email address is (e-mail address removed). Remove the "nop"
from this address. Otto

 

[Otto Moehrbach]

Linda

You asked if all you have to do is run one macro and all the
sheets will be updated. Yes. That is exactly all that you will have to do.
Otto

 

[Guest]

Hi Otto.

I coordinate the weekend volunteers for just certain functions I can
supervise. My current tasks are limited to just "Elect", "Roof", "Plumb",
"Framers",Here are how my tabs are across the bottom of my book

Volunteers Plumb PLSAT PLSUN PLSATSUN Roof ROOFSAT ROOFSUN
ROOFSATSUN Electrical ELECSAT ELECSUN ELECSATSUN FRAME
FRAMESAT FRAMESUN FRAMESATSUN


Each Row of data includes the persons contact information, one task (so more
than one row is used if they can work more than one task) the availability
columns are Sat and anohter column for Sun and then another column for
SatSun. If they can work Sat, the test Sat appears in the Sat column only.

This is my first attempt to set up a recording system so it may not be the
most efficent, but I do think this will work for what I need. When we work
on Saturday, I can run the list of who is availabe for that day for that job.
I hope this makes sence.
Thanks so much Otto....I can't thank you enough.

 

[Otto Moehrbach]

Linda

Might I make a suggestion? Right now you have a sheet for Sat,
another sheet for Sun, and a third sheet for SatSun for each task. That's 3
sheets for each task. Let's say that you want to find someone who is a
plumber and is available on a Saturday. You have to look in the PlumbSat
sheet. BUT, you also have to look in the PlumbSatSun sheet, because anyone
who is available on SatSun is also available on Sat.

Here is what I suggest. Have one sheet, and only one sheet, for
Plumber. In that sheet, have 2 columns for availability, one for Sat, one
for Sun. You have your Volunteer sheet as you have now. It has a button on
it. You click on that button. The code puts all the plumbers in the
Plumber sheet. The code puts Sat in the Sat column if appropriate, Sun in
the Sun column if appropriate, and will put both if that plumber is
available on Sat and Sun. That way you need to look at only one column to
find a plumber who is available for that day. If you wanted to find a
plumber who is available on both days, you would just look for a row that
has both columns filled.

It seems to me that such a format would make your job easier,
but this is your project so let me know.

Also, it would be less messy on the newsgroup if you and I
communicated by email. Let me know about that too. Otto

 

[Otto Moehrbach]

Linda

Here is my first shot at it. All of this code (macros and
declarations) must be placed in the same module.

This code does what I suggested to you. If you will email me with a valid
email address for you I'll send you the file I made up for this that shows
the layout that this code works with as well as all the code placed
properly. My email address is (e-mail address removed). Remove the "nop" from
this address. HTH Otto



Option Explicit

Dim Firsti As Range

Dim i As Range

Dim Dest As Range

Dim RngTasks As Range

Dim ws As Worksheet



Sub CopyAllData()

Dim RngColA As Range

Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))

For Each ws In Sheets(Array("Siding", "Roofing", "Plumbing", _

"Framers", "Electrical", "Interior Finish"))

If RngColA.Offset(, 4).Find(What:=ws.Name, LookAt:=xlWhole) Is
Nothing Then _

GoTo Nextws

With ws

If .[A3] <> "" Then _

.Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset(,
4)).ClearContents

Set Dest = .[A3]

Set RngTasks = RngColA.Offset(, 4)

Set Firsti = RngTasks.Find(What:=ws.Name, _

After:=RngTasks(RngTasks.Count),
LookAt:=xlWhole).Offset(, -4)

Set i = Firsti

Call CopyData

End With

Nextws:

Next ws

End Sub



Sub CopyData()

Dim OffsetS As Long

Do

i.Resize(, 3).Copy Dest

Select Case i.Offset(, 3).Value

Case "": GoTo Nexti

Case "Sat": OffsetS = 3

Case "Sun": OffsetS = 4

Case "SatSun": OffsetS = 5

End Select

If OffsetS = 5 Then

Dest.Offset(, 3) = "Sat"

Dest.Offset(, 4) = "Sun"

Else

Dest.Offset(, OffsetS) = i.Offset(, 3).Value

End If

Set Dest = Dest.Offset(1)

Nexti:

Set i = RngTasks.Find(What:=ws.Name, _

After:=i.Offset(, 4), LookAt:=xlWhole).Offset(, -4)

Loop Until i.Row = Firsti.Row

End Sub