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>
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>
Code:
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