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
