Unable to insert a separately written code into a command button

[Allan91]

Hi guys,

This probably is a very simple question with a very simple solution but I guess I'm too ignorant about VBA yet. I tried copying the below code into a command button but it comes back with an error.

I'd love it if you could explain how to solve this problem since I have many of these codes which actually needs to be in a command button.

Thanks

Option Explicit

Sub ConsolidateExpenses()
Dim wsMonth As Worksheet
Dim wsNew As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim idxMonth As Long
Dim wsCD As Range
Dim wsCD2 As Range
    
    Application.DisplayAlerts = False
    
    Set wsCD = Worksheets("Clean Data").Range("H:H")
    Set wsCD2 = Worksheets("Clean Data").Range("z:z")
    Set wsNew = Sheets.Add

    With wsNew
        .Range("C3:O3").Value = Array("Date", "Expense Amount", "Item", "", "Type of Item", "Item Name", "# of Items", "Item Price", "Income", "Discount", "Discounted Income", "Customer")
        Set rngDst = .Range("C4")
    End With
    
    For idxMonth = 1 To 12
    
        Set wsMonth = Sheets(MonthName(idxMonth, False))
        
        With wsMonth
            Set rngSrc = .Range("F5", .Range("h" & Rows.Count).End(xlUp))
        End With
        
        If rngSrc.Row > 4 Then
            rngSrc.Copy rngDst
            rngDst.Offset(, -1).Resize(rngSrc.Rows.Count) = wsMonth.Name
            Set rngDst = rngDst.Offset(rngSrc.Rows.Count)
        End If
        
    Next idxMonth
    
    Range("B3:N3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$3:$DF$147").AutoFilter Field:=2, Criteria1:="<>"
    ActiveSheet.Range("$f$3:$DF$147").AutoFilter Field:=2, Criteria1:="<>"
    Columns("B:N").Select
    Range("B3").Activate
    Selection.Copy
    Sheets("Raw Data").Select
    Columns("C:C").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Range("A1").Select
    Application.CutCopyMode = False
    
    wsNew.Delete
    
    Application.DisplayAlerts = True
    
    Sheets("Raw Data").Select
    Worksheets("Raw Data").Range("d3:d250").Select
    Worksheets("Raw Data").Range("d3:d250").Copy
    Worksheets("Clean Data").Activate
    Worksheets("Clean Data").Range("h3").Select
    Worksheets("Clean Data").Paste
    Selection.Columns.AutoFit
    Columns("H:H").Select
    Range("H15").Activate
    With Selection.Font
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
    End With

    Sheets("Raw Data").Select
    Worksheets("Raw Data").Range("f3:f250").Select
    Worksheets("Raw Data").Range("f3:f250").Copy
    Worksheets("Clean Data").Activate
    Worksheets("Clean Data").Range("z3").Select
    Worksheets("Clean Data").Paste
    Selection.Columns.AutoFit
    Columns("z:z").Select
    Range("z15").Activate
    With Selection.Font
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
    End With
    
    Application.CutCopyMode = False

    wsCD.RemoveDuplicates Columns:=1, Header:=xlYes
    wsCD2.RemoveDuplicates Columns:=1, Header:=xlYes

    Application.CutCopyMode = False
    
    Sheets("Expense Analysis Dashboard").Select
     
        
End Sub

 

[mohadin]

Replace

Sub ConsolidateExpenses()

With

Private Sub CommandButton1_Click()

 

[Allan91]

Replace

Sub ConsolidateExpenses()

With

Private Sub CommandButton1_Click()

That's the first thing I have tried but to no avail.

The error message I am getting is

Runtime error '1004':

Select method of range class failed.

 

[mohadin]

So may be the
CommandButton1 name is deferent?

 

[Allan91]

So may be the
CommandButton1 name is deferent?

I thought that too but no. It's the same. I have even tried putting it in a command button after removing the original sub and end sub and just embedding it in the command button's sub and end sub. Doesn't work unfortunately.

 

[mohadin]

Well
Do you have in you file, 12 sheets with month name, non of them is missing?

 

[Allan91]

Well
Do you have in you file, 12 sheets with month name, non of them is missing?

No there was is no problem if when I try to run the code within VBA. The problem only occurs when I put it in a command button.

I have solved my problem by using a call function within the command button code. I'm leaving the solution below for anyone else who encounters this problem. I still don't know how to embed my code within the button but this works just fine too.

Private Sub CommandButton1_Click()

Call ConsolidateExpenses

End Sub

 

[smozgur]

ConsolidateExpenses subroutine is likely in a standard module in your project, so simply changing the sub name to CommandButton1_Click won't work, it should be located in the Userform class module.

However, how you did it above is a good implementation instead of filling the event procedures with the actual code for readability. So, your implementation is correct.