Solved VBA coding in Excel

August 9, 2012 at 02:30:03
Specs: Windows XP
I have produced a worksheet called 'Students' with a different record on each row. When a case is cleared I select 'Y' or "W" in column F. Is it possible to set up a macro that once activated moves all cleared cases to worksheet 2 called 'Cleared'. Ensuring it doesn't delete what is already on worksheet 'Cleared'.

See More: VBA coding in Excel

Report •

#1
August 9, 2012 at 05:47:00
Hi,

Here is one way of doing it, please note that this was done very quickly so you should really go through it modify it to work better.

What i have done is, in the "Student" tab, if column "B" contains a "W", the macro will copy that entire row into the "Cleared" worksheet to the next available row.

This will however need to be modified for your requirements.

Dim nCell  As Range

Sub SortList()
    
    Dim bCell As Range
    Dim Urange, Lrange
    
    Set Urange = Sheets("Students").Range("B1")
    Set Lrange = Sheets("Students").Range(Range("B" & Rows.Count).End(xlUp).Address)
    
    For Each bCell In Range(Urange, Lrange)

        If bCell.Value = "W" Then
            
            Sheets("Students").Select
            bCell.Select
            Rows(ActiveCell.Row).Select
            Selection.Copy
            
            Sheets("Cleared").Select
            Sheets("Cleared").Range("A" & GetLast).Select
            Sheets("Cleared").Paste
            
        End If
    Next bCell
    
End Sub

Function GetLast() As Integer
    
    GetLast = Empty
    
    Set nCell = Sheets("Cleared").Range(Range("A" & Rows.Count).End(xlUp).Address)
    
    nCell = nCell + 1
    
    
   
    GetLast = nCell
    
End Function


Report •

#2
August 9, 2012 at 06:38:35
✔ Best Answer
I have not tested this because I do not have access to Excel at this time, but I'd like to offer a coding tip.

Rarely, if ever, do you have to Select an object in VBA in order to perform an action on it. Selecting objects makes the VBA code cumbersome and inefficient.

This section...

      If bCell.Value = "W" Then
            
            Sheets("Students").Select
            bCell.Select
            Rows(ActiveCell.Row).Select
            Selection.Copy
            
            Sheets("Cleared").Select
            Sheets("Cleared").Range("A" & GetLast).Select
            Sheets("Cleared").Paste
            
        End If

...can probably be reduced to this:

        If bCell.Value = "W" Then
            
            Sheets("Students").Range(bCell.Address).EntireRow.Copy _
              Destination:=Sheets("Cleared").Range("A" & GetLast)
            
        End If

However, I see some things in the code that don't seem to address the question.

The OP said that he is entering Y or W in Column F. I don't see any code that checks for a "Y" nor do I see any code that references Column F.

In addition, I'm not sure what your GetLast function is supposed to do. If you are trying to return the next empty Row in the Cleared sheet, I don't think that it will work, but again, I haven't tested it.

If I were trying to return the next empty row in the Cleared sheet, I'd do it right inside the code instead of jumping out into a function.

Something like:

 nxtRow = Sheets("Cleared").Range("F" & Rows.Count).End(xlUp).Row + 1

So perhaps the code would something like this:

        If bCell.Value = "W" Or bCell.Value = "Y" Then
         
           nxtRow = Sheets("Cleared").Range("F" & Rows.Count).End(xlUp).Row + 1
            
              Sheets("Students").Range(bCell.Address).EntireRow.Copy _
                Destination:=Sheets("Cleared").Range("A" & nxtRow)
            
         End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
August 9, 2012 at 06:52:28
Hi DerbyDad,

I just tested my code and it does seem to work, maybe a little cluncky but it does the job. As mentioned above the OP needed to modify the code to fit their requirment as i was not in a position to write the full code and test.

Thank you for the correction, i have some bad habbits when i code, and to be honest i didnt know of the alternative methods. the GetLast does return the next row without error, but i see your method above it much better.

at least i am no longer using the below anymore thanks to your advice. I am still learning and will keep what you have said above in mind.

Range("A65536").End(xlUp).Row


Thanks,


Report •

Related Solutions

#4
August 30, 2012 at 01:49:00
Thank to both of you for helping out with proposal. It seems that DerbyDad code looks more appropriate to what i need. It has referenced the "WW and the "F" column i refered to in my original post. I have to admit that i have not tried as my Excel application has crashed and need to be repaired before i can try.

Can i just DerbyDad to advise if the code should be written inside the code area of the students sheet? And how do i start. Sorry to ask this basic question but it is part of learning.
Many thanks


Report •

#5
August 30, 2012 at 01:57:20
Hi DerbyDad,
What do you think the code should be wrtitten to be successful?

Report •

#6
August 31, 2012 at 09:48:33
this was helpful, thanks

Report •

Ask Question