Run a macro in another sheet of excel

[AnaFlorTech]

Hello,

I have an excel workbook with 3 sheets.
In the first one, I have listed all the requests that were made to me. And one of the column of sheet 1 has the status (Made, Pending).
The second have the subset of requests that are already done and the third sheet has the requests that are still pending.

I've created two macros to update sheets 2 and 3. If the column of status in sheet 1 is Done it goes to sheet 2 and if it's pending, it goes to sheet 3.

I don't want to have any button to run the macros.
What I want is if the column of status changes in sheet one, it will run the two macros. There are two thing that I can do in sheet 1, update an existing record or insert a new one.

How can I do that?

Than ks in advance.

 

[SkipVought]

Hi,

Use the Worksheet_Change event to run the macros.

Need to know the heading of the column that you want the change to fire on.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

Hello,

The heading of the column is "Status" and is the G column.

Thanks.

 

[SkipVought]

So when the user changes any value in column G...

Private Sub Worksheet_Change(ByVal Target As Range)
  if not intersect(target, range("G:G")) is nothing then
     'call your macro
  end if
End Sub

call your macro.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

Hello,

I did what you said:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G:G")) Is Nothing Then
Call STATUS_DONE
End If

End Sub

But when I change column G for one record to DONE in sheet 1 the macro doesn't run. I still don't have in sheet 2 the new done record.

Do I have to do something else?

Thanks

 

[SkipVought]

post your macro code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

Here it is:

Sub STATUS_DONE()
'
' STATUS_DONE Macro
'

'
Range("A1:L1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=24
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-3
Range("A1").Select
Sheets("Requests").Columns("A:L").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Requests").Range("O1:O2"), CopyToRange:=Range( _
"A1"), Unique:=False
End Sub

 

[SkipVought]

Sub STATUS_DONE()
'
' STATUS_DONE Macro
'

'
'[b]if your CHANGE event is on Requests and this code is to run on Sheet2...[/b]

    With Sheets("Sheet2")
        With .Range("A1:L1")    'this range refers to the sheet object in the outer With
            .Range(.Cells, .Cells.End(xlDown)).ClearContents
        End With
        
        'this copies from Requests while the CopyToRange refers to the outer With object
        Sheets("Requests").Columns("A:L").AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Sheets("Requests").Range("O1:O2"), _
            CopyToRange:=.Range("A1"), Unique:=False
    End With
End Sub

NOTICE: the use of the With...End With construct, whch allows reference to that object from the code within.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

Hello,

Now the macro doesn't run anymore. Not even when I run it mannualy. And when I change a value in Sheet 1 it doesn't do anything also.

 

[SkipVought]

I assumed that your sheet1 was named Requests.

Please specifically identify 1) the sheet that you are CHANGING a value in column G, 2) the sheet that your Filtered table is in and 3) the sheet that you are pasting the filtered data.

You have not been very forthright with relevant information. I have to ask for every bit of vital information!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

I'm sorry.Is that I am a little bit confused for so many changes that I've made for run this

1) the sheet that you are CHANGING a value in column G
Sheet 1 named Requests

2) the sheet that your Filtered table is in
Sheet 1 named Requests. In here I've created a table with columns STATUS_VALUE and the following formula:
=ISNUMBER(SEARCH("DONE";G2))

3) the sheet that you are pasting the filtered data
Sheet 2 named COMPLETED

Thanks for all your help

 

[SkipVought]

did you not notice this comment in the code I posted...

[b]
'if your CHANGE event is on Requests and this code is to run on Sheet2...[/b]

Did you chage Sheet2 in the

    With Sheets("Sheet2")

with your exact sheet name, AnaFlorTech (Programmer)? I also assumed that as a programmer, you would understand.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

I did all that but It still doesn't work.

 

[SkipVought]

Well it all worked in my test.

Put a break in your event code an step thru, using the Watch Window to discover what's happening...

faq707-4594.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnaFlorTech]

Can I send you my excel file for you to see what's happening please?
I'm sorry but I don't know much about vba.

Thanks

 

[SkipVought]

ii36250

at

bellhelicopter

dot

textron

dot

com

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

nice doggie


unknown