so i have this macro that loops through 9 tabs in a sheet, refreshing a separate query (from access) in each tab. Then it goes into each tab and does a simple replace (anything with entire cell=0, replace with "No Target"), then a sort and that's it.
When I step through it, everything works perfectly.
The problem is when I run it via a button. It does everything fine, but then at the end it somehow reverts the replace back to the original. It's so weird. So the No Targets revert back to 0s.
Can someone explain this? I've put the code below, and for space/simplicity reasons, I've set it to only do the first 2 tabs, but really there are 9.
When I step through it, everything works perfectly.
The problem is when I run it via a button. It does everything fine, but then at the end it somehow reverts the replace back to the original. It's so weird. So the No Targets revert back to 0s.
Can someone explain this? I've put the code below, and for space/simplicity reasons, I've set it to only do the first 2 tabs, but really there are 9.
Code:
Sub Format()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 2
ActiveSheet.Next.Select
Range("A13").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
Range("A12").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A13").Select
Next i
Sheets("MIS").Select
Range("F13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="0", Replacement:="No Target", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("F:F").EntireColumn.AutoFit
Range("A13").Select
Sheets("BDD").Select
Range("E13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="0", Replacement:="No Target", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:E").EntireColumn.AutoFit
Range("A13").Select
Application.ScreenUpdating = True
End Sub