Unable to insert a separately written code into a command button

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
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

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Replace
VBA Code:
Sub ConsolidateExpenses()
With
VBA Code:
Private Sub CommandButton1_Click()
 

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Replace
VBA Code:
Sub ConsolidateExpenses()
With
VBA Code:
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
So may be the
CommandButton1 name is deferent?
 

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Well
Do you have in you file, 12 sheets with month name, non of them is missing?
 

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
Private Sub CommandButton1_Click()

Call ConsolidateExpenses

End Sub
 
Solution

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,416
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,124
Messages
5,640,250
Members
417,131
Latest member
Seanr19871

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
Top