Is there a formula I can use that would sort information from a master
sheet into up to 4 different worksheets in the same workbook? Right
now I'm engineering IF statements, but I've come across the problem of
blank rows. To get rid of this, I fixed the false value as "zzzz" and
then sorted (because when sorting with " " as the false value, the
values end up on the bottom of the worksheet) but when using the Find
and Replace option, it replaced the zzzz values in the formulas, which
defeats the purpose. Are there any other options or any ways to tweak
this? Thanks!

You'd probably be better off doing this with a VBA macro. Can you post a
sample of your data and your sort criteria?

"Maria" wrote:

Is there a formula I can use that would sort information from a master
sheet into up to 4 different worksheets in the same workbook? Right
now I'm engineering IF statements, but I've come across the problem of
blank rows. To get rid of this, I fixed the false value as "zzzz" and
then sorted (because when sorting with " " as the false value, the
values end up on the bottom of the worksheet) but when using the Find
and Replace option, it replaced the zzzz values in the formulas, which
defeats the purpose. Are there any other options or any ways to tweak
this? Thanks!

On Master Worksheet:
Last Name Color
Baker Red
Jones Blue
Smith Yellow
Winters Blue

I have about 250 names and a lot more columns than this, but, in this
case, the colors represent the sort criteria. I have been using IF
statements to copy the entire row on another worksheet, as I mentioned
earlier. For example, =IF(Master!$AJ3="Blue",Master!A3,"zzzz"). I
have no experience with macros so I would need a step-by-step
explanation, but if they are a better way to go, then I welcome them.
Any help with this would be greatly appreciated. Thanks!

OK, here you go.

In this example, I named the worksheets "Master", "Blue", "Yellow", and
"Red". (In case you don't know how to do that, right click on the tab and
select Rename from the option list. Not to be condescending, but you never
know what people know.)

After you have your sheets properly named, type <Alt F11 which will open up
your Visual Basic editor. Once open, click <Insert <Module up at the top.
A white window will open on the right side of the screen. Copy and paste the
code below (starting with "Sub Sort()" and ending with "End Sub") into that
window. You'll have to go through the code and modify the sheet names and
the cell ranges to suit your particular case, but the comments in the code
should guide you through the process. (Comments will show up in green text in
your VBA editor).

Once you've done that, click the blue arrow button at the top (it looks like
the "Play" button in the Windows Media player) to run the code. If you want
to step through the code to see how it works, push the F8 key. That will
step through one line at a time.

I'd suggest you try this first with your dummy data just to get a feel for
it. Welcome to VBA. It's powerful, and a lot of fun.



Sub Sort()

'This simple macro will sort data onto different worksheets based on
criteria in
'the cell in column B

Dim x, z

On Error Resume Next 'if something goes wrong, it will skip to the next line

Worksheets("Master").Activate 'Rename the sheet to suit. Be sure to leave
the quotation marks

Application.ScreenUpdating = False


'Turns off the screen updating. This makes the code run faster and the
flashing
'sheets won't drive you bonkers. Stepping through the code with F8 ignores
this command
'so you'll still be able to see it work if you step through

Cells(1, 1).Activate

x = 2

Do Until Cells(x, 1).Value = "" ' This steps through the first column until
it hits a blank. X is the row number

'modify this Range statement to gather the entire row of data
'Cell references in VBA are Cells(Row,Column)
'be careful, because it's easy to get it backward

Range(Cells(x, 1), Cells(x, 2)).Copy

'Select the proper worksheet based on color criteria
'Likewise, you will need to ensure you are referring to the cell
'that contains your sort criteria, and also change the Case statement
accordingly

Select Case Cells(x, 2).Value

Case "Red"

Worksheets("Red").Activate

Cells(1, 1).Activate

'this IF function (and those like it) finds the next open row

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If

Cells(z, 1).PasteSpecial 'Pastes your range into the appropriate open row

Case "Yellow"

Worksheets("Yellow").Activate

Cells(1, 1).Activate

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If

Cells(z, 1).PasteSpecial

Case "Blue"

Worksheets("Blue").Activate

Cells(1, 1).Activate

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1

End If

Cells(z, 1).PasteSpecial

End Select

Worksheets("Master").Activate

x = x + 1

Loop

Application.ScreenUpdating = True 'turns screen updating back on

End Sub

"Maria" wrote:

On Master Worksheet:
Last Name Color
Baker Red
Jones Blue
Smith Yellow
Winters Blue

I have about 250 names and a lot more columns than this, but, in this
case, the colors represent the sort criteria. I have been using IF
statements to copy the entire row on another worksheet, as I mentioned
earlier. For example, =IF(Master!$AJ3="Blue",Master!A3,"zzzz"). I
have no experience with macros so I would need a step-by-step
explanation, but if they are a better way to go, then I welcome them.
Any help with this would be greatly appreciated. Thanks!

Thanks so much. I'm going to try this. I have a feeling I'll have
some questions when I actually plug this into the real worksheet.
Thanks again :)

I've adapted the code for my purposes without success and I have a few
questions. They may seem ridiculous, but I have no previous experience
and the original code helped a lot. This is what I've done:

Sub Sort()



Dim x, z 'What does this mean?


On Error Resume Next

Worksheets("Master").Activate

Application.ScreenUpdating = False

'what is screen updating?

Cells(1, 1).Activate


x = 52 'changed because I have 52 columns

Do Until Cells(x, 5).Value = "" ' This steps through the first column
until
'it hits a blank. X is the row number
'changed to (x, 5) because data contains blank columns, however, 5
blank rows
'would only occur after information is completed


Range(Cells(x, 1), Cells(x, 2)).Copy 'what does this mean?


Select Case Cells(x, 36).Value 'changed to 36 because the sorting
criteria is in
'column 36


Case "Red"


Worksheets("Red").Activate


Cells(1, 1).Activate


'I do not understand this if function

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial 'why z?


Case "Blue"


Worksheets("Blue").Activate


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial


Case "Yellow"


Worksheets("Yellow").Activate


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1


End If


Cells(z, 1).PasteSpecial


End Select


Worksheets("Master").Activate


x = x + 1 'What does this do?


Loop


Application.ScreenUpdating = True


End Sub

I've answered your questions inside the code next to your questions. I also
rewrote the code and posted it at the bottom. Hopefully it will fix your
problem, and I eliminated some redundant lines (better programming).


Sub Sort()



Dim x, z 'What does this mean?
This declares the variables so the program recognizes them and can use them


On Error Resume Next

Worksheets("Master").Activate

Application.ScreenUpdating = False

'what is screen updating?
It's the same as Screen Refresh for all practical purposes. If you didn't
have this line in here, the spreadhseet would flash like crazy as it flipped
back and forth between worksheets. You can try it by commenting out this
line (put a single quote in front of it) and running the program. That rapid
flipping back and forth between the sheets eats up CPU, since the computer is
dedicating resources to 'drawing' the screen every time it does something.
Turning off the screen refresh allows the program to run much faster, and you
don't get a headache from watching the screens bounce back and forth.


Cells(1, 1).Activate


x = 52 'changed because I have 52 columns

This is where you went wrong. x is the row, not the column (easy to get
confused, as Visual Basic cell references are written Cells(Row,Column) and
you're used to thinking of them as "A5", in other words (column,row). I set
the original value of x to 2 to account for your header row --the first row
with real data is row 2 (right?).

Do Until Cells(x, 5).Value = "" ' This steps through the first column
until
'it hits a blank. X is the row number
'changed to (x, 5) because data contains blank columns, however, 5
blank rows
'would only occur after information is completed

That is correct, if column 5 is populated all the way down to the end of the
data

Range(Cells(x, 1), Cells(x, 2)).Copy 'what does this mean?

This copies cells(x,1) to cells(x,2), as if you took your mouse, clicked on
A1 and dragged it over A2, selecting both cells. If the line you want to
copy goes from, say, A1 to A8, then the line would read:

Range(Cells(1,1), Cells(1,8)).Copy

Since you are going to go down the rows sequentially, we substitute X for
the row number, giving us

Range(Cells(x,1), Cells(x,8)).Copy

then we increment x (using x=x+1 at the bottom) and start the loop over
until we run out of rows with data.



Select Case Cells(x, 36).Value 'changed to 36 because the sorting
criteria is in
'column 36

Using the Select Case function, you need to match the case to the sorting
criteria. Your example used colors, but if you're using numbers, dates, city
name, or anything else, you will have to change the selection criteria after
each Case statement to match the data you're trying to use to sort. If you
are using numbers, don't use the quotation marks. Those are for text-type
variables. You will also have to have a Case statement for each unique value
for your criteria. Again, your example had 4 colors, so I have 4 Case
statements in the code.


Case "Red"


Worksheets("Red").Activate


Cells(1, 1).Activate


'I do not understand this if function

this IF function determines if there is something in cell A2. The
ActiveCell.End(xlDown).Row statement returns the number of the last row with
data in it before a blank row. It starts at the active cell (I activate cell
A1 after the Else statement) and tell the computer to find the last row iwth
data, then add 1, which would be the first blank row. A2 is blank, you would
get an error when the computer looked for the bottom and found the last row
on the spreadsheet (Row 65000, or something like that). In the case that
cell A2 is blank, the program just sets z to equal 2, which we use for the
row number in the paste function.

I did forget to tell you to put your headers onto sheets Red, White, Blue,
etc before you run this code. Sorry.

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial 'why z?

No reason. It's just a unique variable that I used to designate the row to
paste into
You need to keep z separate from x, since x is used to designate the row on
the Master sheet.


Case "Blue"


Worksheets("Blue").Activate


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial


Case "Yellow"


Worksheets("Yellow").Activate


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1


End If


Cells(z, 1).PasteSpecial


End Select


Worksheets("Master").Activate


x = x + 1 'What does this do?

As above, this increments X so the next row on the master sheet is copied
and pasted appropriately.


Here's a new copy of the code, which should work better. I eliminated some
reduncant lines. Go through it first and see that you're copying the
appropriate range and you have your sort/select criteria properly named.

Sub Sort()

Dim x, z

On Error Resume Next

Worksheets("Master").Activate

Application.ScreenUpdating = False

Cells(1, 1).Activate

x = 2

Do Until Cells(x, 5).Value = ""

Range(Cells(x, 1), Cells(x,36)).Copy 'assuming you have 36 columns


Select Case Cells(x, 36).Value ' Be sure to change the criteria as stated
above

Case "Red"

Worksheets("Red").Activate

Case "Blue"

Worksheets("Blue").Activate

Case "Yellow"

Worksheets("Yellow").Activate

End Select

Cells(1, 1).Activate

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If

Cells(z, 1).PasteSpecial

Worksheets("Master").Activate

x = x + 1 'go to the next row on the Master sheet

Loop ' go back to the next row and do it again until out of data


Application.ScreenUpdating = True


End Sub






Loop


Application.ScreenUpdating = True


End Sub

"Maria" wrote:

I've adapted the code for my purposes without success and I have a few
questions. They may seem ridiculous, but I have no previous experience
and the original code helped a lot. This is what I've done:

Sub Sort()



Dim x, z 'What does this mean?


On Error Resume Next

Worksheets("Master").Activate

Application.ScreenUpdating = False

'what is screen updating?

Cells(1, 1).Activate


x = 52 'changed because I have 52 columns

Do Until Cells(x, 5).Value = "" ' This steps through the first column
until
'it hits a blank. X is the row number
'changed to (x, 5) because data contains blank columns, however, 5
blank rows
'would only occur after information is completed


Range(Cells(x, 1), Cells(x, 2)).Copy 'what does this mean?


Select Case Cells(x, 36).Value 'changed to 36 because the sorting
criteria is in
'column 36


Case "Red"


Worksheets("Red").Activate


Cells(1, 1).Activate


'I do not understand this if function

If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial 'why z?


Case "Blue"


Worksheets("Blue").Activate


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial


Case "Yellow"


Worksheets("Yellow").Activate


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1


End If


Cells(z, 1).PasteSpecial


End Select


Worksheets("Master").Activate


x = x + 1 'What does this do?


Loop


Application.ScreenUpdating = True


End Sub

I tried the new code, but it stopped sorting and selected the fourth
row, and I'm not sure why. I have 52 columns and my sorting criteria
is in column 36. Please let me know if something I've changed in the
code does not make sense. Thanks.


Sub Sort()


Dim x, z


On Error Resume Next


Worksheets("Master").Activate


Application.ScreenUpdating = False


Cells(1, 1).Activate


x = 2


Do Until Cells(x, 8).Value = ""


Range(Cells(x, 1), Cells(x, 52)).Copy


Select Case Cells(x, 36).Value

Case "Red"


Worksheets("Red").Activate


Case "Blue"


Worksheets("Blue").Activate


Case "Yellow"


Worksheets("Yellow").Activate


End Select


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial


Worksheets("Master").Activate


x = x + 1 'go to the next row on the Master sheet


Loop


Application.ScreenUpdating = True


End Sub

From the looks of things, I would say that you have a blank cell in row 4,
column 8 (H8). Since your sort criteria is in column 36 (AJ), try changing
the line

Do Until Cells(x,8).Value = "" to

Do Until Cells(x,36).Value = ""

That Do..Until statement will stop the program as soon as it encounters a
blank cell in Row X, Column 8. I assume you have sort criteria in every row,
so changing the column value from 8 to 36 should alleviate the problem. If
you have entire rows that are blank, then that's a different issue. You'll
need to have one column, even a hidden one, all the way down to the bottom of
your data, with something in it. Use that column for the Do..Until
statement.


"Maria" wrote:

I tried the new code, but it stopped sorting and selected the fourth
row, and I'm not sure why. I have 52 columns and my sorting criteria
is in column 36. Please let me know if something I've changed in the
code does not make sense. Thanks.


Sub Sort()


Dim x, z


On Error Resume Next


Worksheets("Master").Activate


Application.ScreenUpdating = False


Cells(1, 1).Activate


x = 2


Do Until Cells(x, 8).Value = ""


Range(Cells(x, 1), Cells(x, 52)).Copy


Select Case Cells(x, 36).Value

Case "Red"


Worksheets("Red").Activate


Case "Blue"


Worksheets("Blue").Activate


Case "Yellow"


Worksheets("Yellow").Activate


End Select


Cells(1, 1).Activate


If Cells(2, 1).Value = "" Then
z = 2
Else
z = ActiveCell.End(xlDown).Row + 1
End If


Cells(z, 1).PasteSpecial


Worksheets("Master").Activate


x = x + 1 'go to the next row on the Master sheet


Loop


Application.ScreenUpdating = True


End Sub

Well, the code is working, but it stops when all three worksheets have
filled two rows...
The code I'm using is identical to the one I posted with the exception
of Do Until Cells(x,36).Value = ""

Regarding the previous problem I was having (when the code stopped
after row 4), it turns out row 4, column 8 was not blank.

Thanks for all the help so far. Any ideas??

Hmmm.... This _shouldn't_ make any difference, but you can try moving

Cells(1, 1).Activate

into the If statement thus:


If Cells(2, 1).Value = "" Then
z = 2
Else
Cells(1, 1).Activate
z = ActiveCell.End(xlDown).Row + 1
End If

My only other thought is, and this is highly improbable, that you have two
Reds, two Yellows, and two Blues, everything else does not meet any selection
criteria and is ignored. Can you try e-mailing me your spreadhseet so I can
lookstep through the data and see what is amiss?



"Maria" wrote:

Well, the code is working, but it stops when all three worksheets have
filled two rows...
The code I'm using is identical to the one I posted with the exception
of Do Until Cells(x,36).Value = ""

Regarding the previous problem I was having (when the code stopped
after row 4), it turns out row 4, column 8 was not blank.

Thanks for all the help so far. Any ideas??

I have tried emailing the spreadsheet to you at
, but the email will not go through to
that address. Please reply to my email address so I can send it to
you. Thanks.

Maria,

What threw the code off is you had a "Green" in the criteria column. The
code didn't find anything to do with it in the "Select Case" statement, so it
stopped.

I've fixed it in the sample sheet, and put in another two worksheets labeled
"Green" and "Other" so the greens will sort ot ht eGreen sheet, and
everything else (Purple, Black, Orange) will go onto the "Other" sheet.

I've e-mailed the sheet back to you, but will post the code here also for
others who might be following this thread. I also put a comand button on
your master sheet, so all you have to do is click the button to run the sort
macro.

Sub Sort()

'This simple macro will sort data onto different worksheets based on
criteria in
'the cell in column B

Dim x, z

On Error Resume Next 'if something goes wrong, it will skip to the next line

Worksheets("Master").Activate 'Rename the sheet to suit. Be sure to leave
the quotation marks

Application.ScreenUpdating = False


'Turns off the screen updating. This makes the code run faster and the
flashing
'sheets won't drive you bonkers. Stepping through the code with F8 ignores
this command
'so you'll still be able to see it work if you step through

Cells(1, 1).Activate

x = 3

Do Until Cells(x, 1).Value = "" ' This steps through the first column until
it hits a blank. X is the row number

'modify this Range statement to gather the entire row of data
'Cell references in VBA are Cells(Row,Column)
'be careful, because it's easy to get it backward

Range(Cells(x, 1), Cells(x, 52)).Copy

'Select the proper worksheet based on color criteria
'Likewise, you will need to ensure you are referring to the cell
'that contains your sort criteria, and also change the Case statement
accordingly

Select Case Cells(x, 32).Value

Case "Red"

Worksheets("Red").Activate
Case "Yellow"

Worksheets("Yellow").Activate

Case "Blue"

Worksheets("Blue").Activate

Case "Green"

Worksheets("Green").Activate

Case Else

Worksheets("Other").Activate

End Select


Cells(3, 1).Activate

'this IF function (and those like it) finds the next open row

If Cells(3, 1).Value = "" Then
z = 3

ElseIf Cells(4, 1).Value = "" Then

z = 4

Else
z = ActiveCell.End(xlDown).Row + 1
End If

Cells(z, 1).PasteSpecial 'Pastes your range into the appropriate open row



Worksheets("Master").Activate

x = x + 1

Loop

Application.ScreenUpdating = True 'turns screen updating back on

End Sub

--
HTH

JonR


"Maria" wrote:

I have tried emailing the spreadsheet to you at
, but the email will not go through to
that address. Please reply to my email address so I can send it to
you. Thanks.