Multiple Macros under One Button

onexc · Apr 20th 2017

Hello Forum,

This is my code in Module1

Code

Sub Rand()
Dim ws As Worksheet
Dim stRow As Long, endRow As Long, dataCol As Long
Dim dispRow As Long, dispCol As Long


Set ws = Sheets("Sheet1")
stRow = 3
dataCol = 1
dispRow = 4
dispCol = 7


    With ws
        endRow = .Cells(.Rows.Count, dataCol).End(xlUp).Row
        .Cells(dispRow, dispCol).Value = _
          .Cells(Application.RandBetween(stRow, endRow), dataCol).Value
          
    End With
End Sub

Display More

This code is embedded into Button 1. I'd like to add two macros to run when Button 1 was clicked:

Code

Target Private Sub Workbook_SheetSelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
Target.Range("G4").Copy
End Sub

and

Code

Sub Lister() 
    Dim lRow As Long 
    lRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, 1).End(xlUp).Row 
    Worksheets("Sheet2").Range("A" & lRow + 1 & ":C" & lRow + 1).Value = Worksheets("Sheet1").Range("F3:H3").Value 
End Sub

The very first one works like a charm already; random selections.

Other two codes (Thanks Ozgrid's Carim for the last one) also work.. I tried to add / embed to codes into the Module1 but no success.

What I want to do is when clicked once, random selection will be displayed, the value is to be copied to the clipboard, and random selection cells will be copied to another sheet.

Any help would be great. Thanks in advance.

Onexc

**KjBox** · Apr 21st 2017

Re: Multiple Macros under One Button

Try this.

In a standard module:

Code

Sub Rand()
    Dim ws As Worksheet
    Dim stRow As Long, endRow As Long, dataCol As Long
    Dim dispRow As Long, dispCol As Long
     
    Set ws = Sheets("Sheet1")
    stRow = 3
    dataCol = 1
    dispRow = 4
    dispCol = 7
     
    With ws
        endRow = .Cells(.Rows.Count, dataCol).End(xlUp).Row
        With .Cells(dispRow, dispCol)
            .Value = .Cells(Application.RandBetween(stRow, endRow), dataCol).Value
            .Copy
        End With
    End With
    
End Sub


Sub Lister()
    Dim lRow As Long
    
    With Sheets("Sheet2")
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(lRow, 1).Resize(, 3) = Sheets("Sheet1").Range("F3:H3")
    End With
    
End Sub

Display More

In the Worksheet Object Module for the sheet that has the button:

Code

Sub Button1_Click()
    Rand
    Lister
End Sub

Then assign the Button1_Click procedure to the button.

onexc · Apr 22nd 2017

Re: Multiple Macros under One Button

Hello KjBox,

It's very nice to hear from you again. Also thanks for your prompt reply. I am receiving this error when I run the macro: Compile Error: Sub or Function not defined.

Although everything seems fine. Am I missing something?

**KjBox** · Apr 23rd 2017

Re: Multiple Macros under One Button

Did you delete your existing macros before installing the ones I provided? If not then delete them and try again.

onexc · Apr 23rd 2017

Re: Multiple Macros under One Button

Hi Kj,

Still does not work and couldn't find out how. Please see the file, Kj, as attached.

onexc · May 7th 2017

Re: Multiple Macros under One Button

Hello Kj,

Did you check the file? Thank you in advance.

**Krishnakumar** · May 8th 2017

Re: Multiple Macros under One Button

Hi

If you read Kj's post again, he has explicitly said, put the code in a standard module. Your code is in Worksheet module. Remove the code from sheet module and put it in standard module.

onexc · May 14th 2017

Re: Multiple Macros under One Button

Friends, I really did. What's wrong with it? Please help.

**Krishnakumar** · May 15th 2017

Re: Multiple Macros under One Button

Can you show what you have done ? attach the workbook.

**KjBox** · May 15th 2017

Re: Multiple Macros under One Button

You had put the wrong procedures in the wrong modules. Your code was not working anyway.

Try the attached.

Code assigned to the button is in the Worksheet Object Module for Sheet1, and is

Code

Sub Button2_Click()
    GetRandomRowData
End Sub

In a standard module is

Code

Sub GetRandomRowData()
    Dim x, lRow As Long
    
    With Sheets("Sheet1").[a3].CurrentRegion
        lRow = Application.RandBetween(2, .Rows.Count)
        x = .Cells(lRow, 1).Resize(, 3)
    End With
    With Sheets("Sheet2")
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(lRow, 1).Resize(, 3) = x
    End With
    
End Sub

Display More

Clicking the button will place a randomly selected row of Sheet 1 data onto the next available row on Sheet 2

onexc · May 15th 2017

Re: Multiple Macros under One Button

Thank you very much KjBox for your effort as well as Krishnakumar for your patient and support. This works like a charm.

**KjBox** · May 16th 2017

Re: Multiple Macros under One Button

You're welcome

onexc · May 21st 2017

Re: Multiple Macros under One Button

Just a very little question KjBox... Can we setup random selection with row number? Because there are same numbers in rows and columns that row numbers can make things easier for me.

**KjBox** · May 21st 2017

Re: Multiple Macros under One Button

Sorry, I don't understand what you mean, the code randomly selects a row number between 2 and the total number of rows used on the sheet.

What do you mean by "random selection by row number"? That is what is happening already.

onexc · May 21st 2017

Re: Multiple Macros under One Button

Quote from KjBox;792714

Sorry, I don't understand what you mean, the code randomly selects a row number between 2 and the total number of rows used on the sheet.

What do you mean by "random selection by row number"? That is what is happening already.

You are totally right. It's happening in that way already. What I kindly ask that how you can also display row number of the random selection?

**KjBox** · May 22nd 2017

Re: Multiple Macros under One Button

Display it where?

onexc · May 31st 2017

Re: Multiple Macros under One Button

Quote from KjBox;792739

Display it where?

Hello KjBox,

For instance, Sheet2 D2... The same with copied rows.

**KjBox** · Jun 1st 2017

Re: Multiple Macros under One Button

Try the attached. It will show the Data Row Number for the row that corresponds to the random number that was generated. This excludes the header row.

onexc · Jun 1st 2017

Re: Multiple Macros under One Button

Quote from KjBox;793277

Try the attached. It will show the Data Row Number for the row that corresponds to the random number that was generated. This excludes the header row.

Thank you very much KjBox. This is exactly what I needed, indeed.

onexc · Jun 4th 2017

Re: Multiple Macros under One Button

Hi KjBox, very very last question on this matter: Sheet1 Column A numbers has a hypen in the second digit from right like: 1256789-8 or 5684191-5. when this was randomly picked, in the Sheet2 Column, how can I display as 1256789 or 5684191-5; hypen and last digit removed. Thank you very much again.

Multiple Macros under One Button

Participate now!

Share