Macro Doesn't Work When Assigned To Button

M

mlo356

Board Regular
Joined
Aug 20, 2015
Messages
51
Hi All,

I have a macro that work fine run from the editor but seems to skip a step when assigned to button. the part it seems to skip is

Code: 
LRC = Sheets("Changes").Cells(Rows.Count, 1).End(xlUp).Row
        
                    If Count2 > 0 Then
                    Range("$A$1:$P$" & LRN).AutoFilter Field:=16, Criteria1:="YES"
                    Range("A2:O2").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                    Sheets("Changes").Select
                    Range("A" & LRC + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


                    LRC = Sheets("Changes").Cells(Rows.Count, 1).End(xlUp).Row


                    Else
                    End If

Full code is below. Anyone have any idea why it works from VBA edotior and not a button?

Thanks!!

Code: 
Option Explicit

Sub CompareAndReport()
'
' Compare and Report


Dim SaveAsDialog As Object
Dim MWB
Dim NWB
Dim ws As Worksheet
Dim LRN
Dim LRO
Dim LRC
Dim Count1
Dim Count2
Dim SavePath As String
Dim Count3


    'Create new wprkbook with Import amd Pre-import tabs
    'Application.ScreenUpdating = False


    Sheets("Pre-import").Select


    Set SaveAsDialog = Application.FileDialog(msoFileDialogSaveAs)
    Set MWB = ThisWorkbook


    Sheets(Array("Pre-import", "Import")).Copy
    
    ActiveWorkbook.Activate
    Set NWB = ActiveWorkbook




    'Create changes worksheet, remove existing validation


    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Changes"




    For Each ws In Worksheets
        ws.Cells.Validation.Delete
    Next ws
    
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Test to make sure that there are changes to report on "Pre-import" tab


   Sheets("Pre-Import").Activate
   
    LRN = Cells(Rows.Count, 1).End(xlUp).Row


    Range("K1").FormulaR1C1 = "New Project"
    Range("K3").FormulaR1C1 = "=IFERROR(IF(ISNUMBER(NUMBERVALUE(MATCH(RC4,Import!R1C4:R102C4,0))),""NO"",""YES""),""YES"")"
    Range("L1").FormulaR1C1 = "Approval Status Change"
    Range("L3").FormulaR1C1 = "=IF(RC11=""YES"",""NA"",IFERROR(IF(RC4="""","""",IF(INDEX(Import!R1C7:R102C7,MATCH(RC4,Import!R1C4:R102C4,0))=RC7,""NO"",INDEX(Import!R1C7:R102C7,MATCH(RC4,Import!R1C4:R102C4,0)))),""NO""))"
    Range("M1").FormulaR1C1 = "Start Date Change"
    Range("M3").FormulaR1C1 = "=IF(RC11=""YES"",""NA"",IFERROR(IF(RC4="""","""",IF(INDEX(Import!R1C9:R102C9,MATCH(RC4,Import!R1C4:R102C4,0))=RC9,""NO"",INDEX(Import!R1C9:R102C9,MATCH(RC4,Import!R1C4:R102C4,0)))),""NO""))"
    Range("N1").FormulaR1C1 = "Finish Date Change"
    Range("N3").FormulaR1C1 = "=IF(RC11=""YES"",""NA"",IFERROR(IF(RC4="""","""",IF(INDEX(Import!R1C10:R102C10,MATCH(RC4,Import!R1C4:R102C4,0))=RC10,""NO"",INDEX(Import!R1C10:R102C10,MATCH(RC4,Import!R1C4:R102C4,0)))),""NO""))"
    Range("O1").FormulaR1C1 = "Completed/Removed Project"
    Range("O3").FormulaR1C1 = "=IF(RC[-11]="""","""",""NO"")"
    Range("P1").FormulaR1C1 = "Exception"
    Range("P3").FormulaR1C1 = "=IF(OR(LEN(RC12)>2,LEN(RC13)>2,LEN(RC14)>2),""YES"",""NO"")"
    
    Range("A1:O1").Copy
    Sheets("Changes").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Changes").Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("K3:P3").AutoFill Destination:=Range("K3:P" & LRN)
    
    On Error Resume Next
    Worksheets("Pre-import").AutoFilterMode = False
    Columns("A:P").AutoFilter
    On Error GoTo 0


    Count1 = WorksheetFunction.CountIf(Range("K2:K5000"), "YES")
    Count2 = WorksheetFunction.CountIf(Range("P3:P5000"), "YES")


'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Test to make sure that there are changes to report on "Import" tab


    Sheets("Import").Activate
    LRO = Cells(Rows.Count, 1).End(xlUp).Row
    Range("K3").FormulaR1C1 = "NO"
    Range("L3").FormulaR1C1 = "NO"
    Range("M3").FormulaR1C1 = "NO"
    Range("N3").FormulaR1C1 = "NO"
    Range("O3").FormulaR1C1 = "=IFERROR(IF(RC[-11]="""","""",MATCH(RC[-11],'Pre-Import'!R1C4:R5000C4,0)),""YES"")"
    Range("K3:O3").AutoFill Destination:=Range("K3:O" & LRO)
    
    On Error Resume Next
    Worksheets("Import").AutoFilterMode = False
    Columns("A:P").AutoFilter
    On Error GoTo 0


    Count3 = WorksheetFunction.CountIf(Range("O2:O5000"), "YES")




'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'If nothing to report, close new workbook and msg the user


    If Count1 = 0 And Count2 = 0 And Count3 = 0 Then '1
    Sheets("changes").Range("A2") = "NO CHANGES FOR THIS DAY"
    
    MsgBox "There are no changes for this day", vbInformation
    
    Else
        
        
'If something to report, populate, format, condition report


            Sheets("Pre-import").Activate
    
            LRC = Sheets("Changes").Cells(Rows.Count, 1).End(xlUp).Row
        
            If Count1 > 0 Then
    
            Range("$A$1:$P$" & LRN).AutoFilter Field:=11, Criteria1:="Yes"
            Range("A2:O2").Select
            Range(Selection, Selection.End(xlDown)).Copy
            Sheets("Changes").Range("A" & LRC + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            ActiveSheet.ShowAllData
        
            Else
            End If
        
                    LRC = Sheets("Changes").Cells(Rows.Count, 1).End(xlUp).Row
        
                    If Count2 > 0 Then
                    Range("$A$1:$P$" & LRN).AutoFilter Field:=16, Criteria1:="YES"
                    Range("A2:O2").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                    Sheets("Changes").Select
                    Range("A" & LRC + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


                    LRC = Sheets("Changes").Cells(Rows.Count, 1).End(xlUp).Row


                    Else
                    End If
                    
            
    
                            Sheets("Import").Select
                    
                            If Count3 > 0 Then
                            Range("$A$1:$O$" & LRO).AutoFilter Field:=15, Criteria1:="YES"
                            Range("A2:O2").Select
                            Range(Selection, Selection.End(xlDown)).Copy
                            Sheets("Changes").Range("A" & LRC + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            Application.CutCopyMode = False


                            Else
                            End If
                            
    End If
    
    On Error Resume Next
    Sheets("Pre-import").Select
    Sheets("Pre-import").ShowAllData
    Sheets("Pre-import").Range("K:P").Delete
    'Application.Goto Reference:=Range("a1"), Scroll:=True
    
    Sheets("Import").Select
    Sheets("Import").ShowAllData
    Sheets("Import").Range("K:P").Delete
    'Application.Goto Reference:=Range("a1"), Scroll:=True
    On Error GoTo 0


    'Formatting
    
    Sheets("Changes").Select
    LRC = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:P" & LRC).AutoFilter
    


    
    'Center Text
    Range("H:O,A:D").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Cells.EntireColumn.AutoFit
    
    'Color fill change section
    Columns("K:O").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    'Format date columns
    Range("M1,N1,I1,J1").EntireColumn.NumberFormat = "m/d/yyyy"
    
    'Condition to highlight changes
    Range("K2:O5000").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(K2)>2"
    Range("K2:O5000").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Range("K2:O5000").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Range("K2:O5000").FormatConditions(1).StopIfTrue = False
    
    Application.Goto Reference:=Range("A1"), Scroll:=True
    


'prompt user to save file
SavePath = MWB.Path
With SaveAsDialog
    .InitialFileName = SavePath & "\" & "Giraffe Scaffold Shedule - Changes - " & Format(Date, "MM.DD.YYYY")
    .Title = "Save File"
    .InitialView = msoFileDialogViewList
        If .Show = True Then
        Application.DisplayAlerts = False
        .Execute
        Application.DisplayAlerts = True
        End If
End With


'Application.ScreenUpdating = True


End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
One thing that I'm noticing is all those unqualified references. Along with the addition as switching between worksheets, it would be best to fully qualify everything.

If your button isn't on the right sheet, those unqualified references are going to trip you up.

Also, have you set a breakpoint and stepped through the routine (when called by button) to see what the value of Count2 is when you get to it?
 
Upvote 0
Thanks for the reply and suggestions Mikerickson. You were correct. The switching back and forth between sheets was the culprit. Going to clean this up a bit and qualify the references.

Thanks Again!!
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Macro to speedy up process when copying Data
Replies
3
Views
110
Logit
Logit
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
364
Logit
Logit
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
805
OilEconomist
O
M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
143
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top