Excel VB question

[Stang02GT]

I know very little about excel and if anyone has looked through the Access forum i am just gettting my feet wet with VB coding.

Is it possible to do VB code in Excel that will look at a column F in all four of my tabs, and if a cell in the column F says "completed" it will be moved to another Tab Called "completed" in my work book?

[IMAPD]

It is possible to Code in Excel . Its called writing macros.
Press Alt+F11 On the excel sheet on which you need to do the coding the editor will open, there you will find various events on which you can write your code.
However VBA is a subset of Vb so you will not find all the features supported by VB.

[Stang02GT]

It is possible to Code in Excel . Its called writing macros.
Press Alt+F11 On the excel sheet on which you need to do the coding the editor will open, there you will find various events on which you can write your code.
However VBA is a subset of Vb so you will not find all the features supported by VB.

I have developed a macro to do this, what event should i place it in?

[MikeTheBike]

I have developed a macro to do this, what event should i place it in?

OK, so you have written the code. How/when/where do you want this to run?

You could just put a button on the sheet and click it !?

It is possible to Code in Excel . Its called writing macros.

BTW as fare as I an concerned a macro is something you record. VBA can do very much more than you can do just clicking buttons on a toolbar. You can write programms in Excel that don't use spreadsheet functionality at all. Why would you? Because I don't have VB6 or .NET available at work.

Sorry about the rant!

MTB

[Stang02GT]

OK, so you have written the code. How/when/where do you want this to run?

You could just put a button on the sheet and click it !?

BTW as fare as I an concerned a macro is something you record. VBA can do very much more than you can do just clicking buttons on a toolbar. You can write programms in Excel that don't use spreadsheet functionality at all. Why would you? Because I don't have VB6 or .NET available at work.

Sorry about the rant!

MTB

I would like it to run as soon as the spread sheet is opened, if i can have it run automatically that would be great, opposed to the user pressing a button.

Here is my code also

Dim lLastRow As Long, lRow As Long, lCompRow As Long
Dim wsShts As Worksheet, wsComplete As Worksheet
 
Set wsComplete = Sheets("Completed")
lCompRow = wsComplete.Cells(Rows.Count, 1).End(xlUp).Row + 1
 
For Each wsShts In ThisWorkbook.Worksheets
 
    If wsShts.Name <> "Completed" Then
        lLastRow = wsShts.Cells(Rows.Count, 1).End(xlUp).Row
        For lRow = 2 To lLastRow
            If UCase(wsShts.Cells(lRow, 6).Value) = "COMPLETED" Then
                wsShts.Range("A" & lRow).EntireRow.Copy
                wsComplete.Range("A" & lCompRow).PasteSpecial
                lCompRow = lCompRow + 1
            End If
        Next lRow
    End If
 
Next wsShts
 

[MikeTheBike]

I would like it to run as soon as the spread sheet is opened, if i can have it run automatically that would be great, opposed to the user pressing a button.

Here is my code also

Dim lLastRow As Long, lRow As Long, lCompRow As Long
Dim wsShts As Worksheet, wsComplete As Worksheet
 
Set wsComplete = Sheets("Completed")
lCompRow = wsComplete.Cells(Rows.Count, 1).End(xlUp).Row + 1
 
For Each wsShts In ThisWorkbook.Worksheets
 
    If wsShts.Name <> "Completed" Then
        lLastRow = wsShts.Cells(Rows.Count, 1).End(xlUp).Row
        For lRow = 2 To lLastRow
            If UCase(wsShts.Cells(lRow, 6).Value) = "COMPLETED" Then
                wsShts.Range("A" & lRow).EntireRow.Copy
                wsComplete.Range("A" & lCompRow).PasteSpecial
                lCompRow = lCompRow + 1
            End If
        Next lRow
    End If
 
Next wsShts
 

OK, so where is this code going be placed?
Is in the book that ThisWorkbook is refering to in the code, or in some other workbook ? If it is the fosrt option, then just place it in the ThisWorkbook module Workbook_Open event, if not, it gets a little more complicated.

Let me know.

MTB

[Stang02GT]

Right now i have the code in a module and would like it to run as soon as the workbook is opened.

[MikeTheBike]

Right now i have the code in a module and would like it to run as soon as the workbook is opened.

Have you tried starting it in the Workbook_Open like I suggested?

MTB

[Stang02GT]

Yes i tried to put it in there like you suggested.

Sorry i will explain to you what the code is trying to do

I have a workbook with 5 tabs one being a completed tab. THis workbook keeps track of projects that are in our business. In the F column of every sheet is a status of the projects. I would like that if the status is changed to "completed" the entire record be moved to the completed tab of the workbook.

[Stang02GT]

Ok i have gotten the code to work. It runs through all my records and copies anythign that is completed and pastes it in the completed tab of the workbook.

Now my issuse is how can i delete the rows that i copied and pasted. Whats the best way to do this...have something that runs through and deletes the records i copied?

here is my code

Sub CompActions()
 
Dim lLastRow As Long, lRow As Long, lCompRow As Long
Dim wsShts As Worksheet, wsComplete As Worksheet
 
Set wsComplete = Sheets("Completed")
lCompRow = wsComplete.Cells(Rows.Count, 6).End(xlUp).Row + 1
 
For Each wsShts In ThisWorkbook.Worksheets
 
    If wsShts.Name <> "Completed" Then
        lLastRow = wsShts.Cells(Rows.Count, 6).End(xlUp).Row
        For lRow = 2 To lLastRow
            If Left(UCase(wsShts.Cells(lRow, 6).Value), 4) = "COMP" Then
                wsShts.Range("A" & lRow).EntireRow.Copy
                wsComplete.Range("A" & lCompRow).PasteSpecial
                lCompRow = lCompRow + 1
            End If
        Next lRow
   End If
 
Next wsShts
 
End Sub

[MikeTheBike]

Hi

Try this and see if it does wat you require

Sub CompActions()
 
Dim lLastRow As Long, lRow As Long, lCompRow As Long
Dim wsShts As Worksheet, wsComplete As Worksheet
 
Set wsComplete = Sheets("Completed")
lCompRow = wsComplete.Cells(Rows.Count, 6).End(xlUp).Row + 1
 
For Each wsShts In ThisWorkbook.Worksheets
 
    If wsShts.Name <> "Completed" Then
        lLastRow = wsShts.Cells(Rows.Count, 6).End(xlUp).Row
        For lRow = 2 To lLastRow
            If Left(UCase(wsShts.Cells(lRow, 6).Value), 4) = "COMP" Then
                wsShts.Range("A" & lRow).EntireRow.Copy
                wsComplete.Range("A" & lCompRow).PasteSpecial
                lCompRow = lCompRow + 1
            End If
        Next lRow
 
        For lRow = lLastRow To 2 Step -1
            If Left(UCase(wsShts.Cells(lRow, 6).Value), 4) = "COMP" Then
                wsShts.Row(lRow).Delete
            End If
        Next lRow
   End If
 
Next wsShts
 
End Sub

??

MTB

[Stang02GT]

Awsome it worked! Thanks Mike!