Macro to run only when cell does not equal "test"

[ElkySS]

So I have been looking around but I cannot find a way to do this like I wanted to. Maybe there is a better way.

I have a workbook that has two sheets in it. "A-M" & "N-Z". When I open the work book I have a macro that runs that goes to each of those sheets and deletes all of the previous data. It then puts the text "Paste Agent Split-Skill Interval Here" in cell A1 on both pages.

I then run another report and copy the result into each sheet and hit save. On save I have 3 other macros that normalize the data on the sheets so that it is something I can use in a database. They get rid of merged cells, fixes the names in the areas where a merged cell was, and so on. Right now it does this on both sheets any time I hit save. So far it all works exactly like I want it to.

My problem is that sometimes I will only need to use one sheet. So when my macros run on the second sheet but none of the data is there for it to unmerge and such I get an error. So I had the thought to go to the sheet page "Sheet1 (A-M)" and put some On change type code there so that anytime the cell A1 does not equal "Paste Agent Split-Skill Interval Here" then run my macros. I am not able to get this to work so that it would only run the macros on the sheets that need them.

Any other ideas? Here is what I have so far.</SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value <> "Paste Agent Split-Skill Interval Here" Then Application.Run "Remove_merge"
If Range("A1").Value <> "Paste Agent Split-Skill Interval Here" Then Application.Run "FillRow"
If Range("A1").Value <> "Paste Agent Split-Skill Interval Here" Then Application.Run "Split_Date"
End Sub

 

[ElkySS]

So after messing with it some more I made it never stop running. Since that is only looking to see if the cell does not have the default text in it wants to run all of the time. I made a really bad loop that crashed excel. lol. The search for a better way continues</SPAN>

 

[shg]

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1").Value <> "Paste Agent Split-Skill Interval Here" Then
        Remove_merge
        FillRow
        Split_Date
    End If
End Sub

 

[ElkySS]

At lunch right now. At first look it is a cleaned up version of mine. Think that will make it only run once? I will give it a shot once I get back in.

 

[ElkySS]

Sitting here at lunch thinking about it and I have that macro calling the other 3 when the save button is clicked. Should I dump the on change and just put the if statement in my on save call? So that the user hits save then it checks cell a1 and if it is not the default then call the others?

 

[shg]

Sorry, I don't have a clue what you're trying to do, why you get errors when cells are not merged (or why they were merged in the first place), or why you need to run the macros in the Change events as opposed to only when needed, or ...

 

[ElkySS]

Long story short:
Moving the If statment to the on save call worked great.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
    Sheets(1).Select
    If Range("A1").Value <> "Paste Agent Split-Skill Interval Here" Then
        Remove_merge
        FillRow
        Split_Date
    End If
    Sheets(2).Select
        If Range("A1").Value <> "Paste Agent Split-Skill Interval Here" Then
        Remove_merge
        FillRow
        Split_Date
    End If
   Application.ScreenUpdating = True
End Sub

Short story long:
I have a report I run in a browser window that gives me some raw data for a few hundred people. Several of the cells are merged 5 - 6 cells tall for things such as Name, emp ID, and such. Some cells are not merged such as multiple stats for each Name. So it has the name listed once in a cell that is 5 rows high. Each of those 5 rows will have data in them to the right of the name. I move the report into excel but then I use Access to create a form from that data so the end user only has to see the form with everything they need laid out in a nice format. So that I can get Access to understand the data I have to unmerge the cells but then Excel only puts the name in the first of the merged cells. Then I have a macro that takes that name and moves it to all of the empty cells below it till it runs into the next name and so on for all hundred or so people. Then I have a sheet with each person’s name listed 5 times with each of the different stats to the right of their name. The report also has one cell that has the start and end date all in one place. I need it to be in two cells so I can do some stuff with it in Access. So I have third macro that runs text to columns on the cells that need it so I have a start date cell and an end date cell. Then I end up with a sheet that access can easily read and for me to run some other calculations against in access.</SPAN>