How to assign a macro from one sheet to a form button on diffrent sheet

R

RBernardo

New Member
Joined
Mar 4, 2014
Messages
5
Hi,
I am running excel 2010 with windows 7. I created a macro in sheet 1 and I wish to activate the macro from sheet 2 using a form button. I have entered the code below. I know how to perform this function on a more simple macro like adding names to cells. This code is a bit more complex I just dont know where to start. Any help would be appreciated.
Code: 
Sub LoanData()
'
' LoanData Macro
'
'
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("B3").Select
    Selection.AutoFilter
    Range("E8").Select
    ActiveSheet.Range("$A$1:$M$1001").AutoFilter Field:=5, Criteria1:=Array( _
        "00978 - Everest University Online, Tempe, AZ", _
        "00988 - Everest Online - Colorado Springs, CO", _
        "00988 - Everest Online - Orlando South, FL"), Operator:=xlFilterValues
    Rows("5:5").Select
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 277
    ActiveWindow.ScrollRow = 310
    ActiveWindow.ScrollRow = 348
    ActiveWindow.ScrollRow = 371
    ActiveWindow.ScrollRow = 394
    ActiveWindow.ScrollRow = 429
    ActiveWindow.ScrollRow = 459
    ActiveWindow.ScrollRow = 511
    ActiveWindow.ScrollRow = 540
    ActiveWindow.ScrollRow = 592
    ActiveWindow.ScrollRow = 623
    ActiveWindow.ScrollRow = 664
    ActiveWindow.ScrollRow = 687
    ActiveWindow.ScrollRow = 727
    ActiveWindow.ScrollRow = 744
    ActiveWindow.ScrollRow = 767
    ActiveWindow.ScrollRow = 782
    ActiveWindow.ScrollRow = 799
    ActiveWindow.ScrollRow = 809
    ActiveWindow.ScrollRow = 813
    ActiveWindow.ScrollRow = 816
    ActiveWindow.ScrollRow = 818
    ActiveWindow.ScrollRow = 824
    ActiveWindow.ScrollRow = 833
    ActiveWindow.ScrollRow = 850
    ActiveWindow.ScrollRow = 867
    ActiveWindow.ScrollRow = 898
    ActiveWindow.ScrollRow = 902
    ActiveWindow.ScrollRow = 913
    ActiveWindow.SmallScroll Down:=531
    Rows("5:1500").Select
    Selection.Delete Shift:=xlUp
    Range("C601").Select
    ActiveSheet.ShowAllData
    Range("E5").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveSheet.Range("$A$1:$M$581").AutoFilter Field:=10, Criteria1:=Array( _
        "Cancelled - School Request - Reapply Option", "Cancelled: Credit Error", _
        "Cancelled-Loan Documents Expired"), Operator:=xlFilterValues
    ActiveWindow.SmallScroll Down:=-6
    Rows("82:82").Select
    Range("H82").Activate
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 173
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 201
    ActiveWindow.ScrollRow = 208
    ActiveWindow.ScrollRow = 219
    ActiveWindow.ScrollRow = 224
    ActiveWindow.ScrollRow = 228
    ActiveWindow.ScrollRow = 233
    ActiveWindow.ScrollRow = 236
    ActiveWindow.ScrollRow = 241
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 251
    ActiveWindow.ScrollRow = 261
    ActiveWindow.ScrollRow = 267
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 294
    ActiveWindow.ScrollRow = 314
    ActiveWindow.ScrollRow = 330
    ActiveWindow.ScrollRow = 359
    ActiveWindow.ScrollRow = 379
    ActiveWindow.ScrollRow = 411
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 477
    ActiveWindow.ScrollRow = 497
    ActiveWindow.ScrollRow = 517
    ActiveWindow.ScrollRow = 543
    ActiveWindow.ScrollRow = 550
    ActiveWindow.ScrollRow = 554
    ActiveWindow.ScrollRow = 557
    ActiveWindow.ScrollRow = 562
    ActiveWindow.ScrollRow = 568
    ActiveWindow.ScrollRow = 574
    ActiveWindow.ScrollRow = 577
    ActiveWindow.ScrollRow = 586
    ActiveWindow.ScrollRow = 596
    ActiveWindow.ScrollRow = 606
    ActiveWindow.ScrollRow = 625
    ActiveWindow.ScrollRow = 643
    ActiveWindow.ScrollRow = 672
    ActiveWindow.ScrollRow = 691
    ActiveWindow.ScrollRow = 720
    ActiveWindow.ScrollRow = 737
    ActiveWindow.ScrollRow = 755
    ActiveWindow.ScrollRow = 765
    ActiveWindow.ScrollRow = 775
    ActiveWindow.ScrollRow = 783
    ActiveWindow.ScrollRow = 792
    ActiveWindow.ScrollRow = 797
    ActiveWindow.ScrollRow = 805
    ActiveWindow.ScrollRow = 809
    ActiveWindow.ScrollRow = 817
    ActiveWindow.ScrollRow = 820
    ActiveWindow.ScrollRow = 823
    ActiveWindow.ScrollRow = 828
    ActiveWindow.ScrollRow = 831
    ActiveWindow.ScrollRow = 835
    ActiveWindow.ScrollRow = 838
    ActiveWindow.ScrollRow = 843
    ActiveWindow.ScrollRow = 846
    ActiveWindow.ScrollRow = 854
    ActiveWindow.ScrollRow = 858
    ActiveWindow.ScrollRow = 871
    ActiveWindow.ScrollRow = 877
    ActiveWindow.ScrollRow = 888
    ActiveWindow.ScrollRow = 894
    ActiveWindow.ScrollRow = 906
    ActiveWindow.ScrollRow = 914
    ActiveWindow.ScrollRow = 923
    ActiveWindow.ScrollRow = 931
    ActiveWindow.ScrollRow = 940
    ActiveWindow.ScrollRow = 946
    ActiveWindow.ScrollRow = 952
    ActiveWindow.ScrollRow = 958
    ActiveWindow.ScrollRow = 963
    ActiveWindow.ScrollRow = 969
    ActiveWindow.SmallScroll Down:=504
    Rows("82:1500").Select
    Range("H82").Activate
    Selection.Delete Shift:=xlUp
    Range("I259").Select
    ActiveSheet.ShowAllData
    Range("J4").Select
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").Select
    Selection.NumberFormat = "0000"
    Columns("H:H").Select
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(7, 1)), TrailingMinusNumbers:=True
    Columns("I:I").Select
    Selection.NumberFormat = "0000"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Identifier"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[8],""0000"")&""""&RC[3]"
    Range("A3").Select
    Columns("A:A").EntireColumn.AutoFit
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1000"), Type:=xlFillDefault
    Range("A2:A1000").Select
    ActiveWindow.ScrollRow = 997
    ActiveWindow.ScrollRow = 987
    ActiveWindow.ScrollRow = 976
    ActiveWindow.ScrollRow = 962
    ActiveWindow.ScrollRow = 941
    ActiveWindow.ScrollRow = 927
    ActiveWindow.ScrollRow = 905
    ActiveWindow.ScrollRow = 888
    ActiveWindow.ScrollRow = 865
    ActiveWindow.ScrollRow = 845
    ActiveWindow.ScrollRow = 813
    ActiveWindow.ScrollRow = 791
    ActiveWindow.ScrollRow = 755
    ActiveWindow.ScrollRow = 732
    ActiveWindow.ScrollRow = 693
    ActiveWindow.ScrollRow = 671
    ActiveWindow.ScrollRow = 622
    ActiveWindow.ScrollRow = 587
    ActiveWindow.ScrollRow = 530
    ActiveWindow.ScrollRow = 490
    ActiveWindow.ScrollRow = 454
    ActiveWindow.ScrollRow = 414
    ActiveWindow.ScrollRow = 395
    ActiveWindow.ScrollRow = 365
    ActiveWindow.ScrollRow = 343
    ActiveWindow.ScrollRow = 313
    ActiveWindow.ScrollRow = 297
    ActiveWindow.ScrollRow = 276
    ActiveWindow.ScrollRow = 257
    ActiveWindow.ScrollRow = 237
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Columns("A:A").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A6").Select
    Application.CutCopyMode = False
    Range("D11").Select
End Sub
 
Last edited by a moderator:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
I have cleaned up the recorded code and removed some unnecessary lines and made it a bit more flexible.
Be sure to test on a copy of your data. Please let me know how it works.

Code in the codepage for a worksheet should generally only be used to manipulate data on that worksheet.
Add a standard module to the workbook (from the menu in the Visual Basic editor) and put this code into it.
Assign the macro name a command button on each worksheet where you want the code to run.

Code: 
Sub LoanData()
'
' LoanData Macro
'
    Dim lLastDataRow As Long
    
    ActiveSheet.AutoFilterMode = False
    
    lLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells.EntireColumn.AutoFit

    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=5, _
        Criteria1:=Array( _
        "00978 - Everest University Online, Tempe, AZ", _
        "00988 - Everest Online - Colorado Springs, CO", _
        "00988 - Everest Online - Orlando South, FL"), _
        Operator:=xlFilterValues
    
    'Conditional delete
    'If there are more than 4 rows visible (assumes column A rows 1-4 contains header cells that contain info)
    If Application.WorksheetFunction.Subtotal(3, Columns(1)) > 4 Then
        'At least one filtered row is visible, delete visible non-header rows
        Rows("5:" & lLastDataRow).Cells.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    End If
    
    ActiveSheet.ShowAllData
    
    lLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=10, _
        Criteria1:=Array( _
        "Cancelled - School Request - Reapply Option", "Cancelled: Credit Error", _
        "Cancelled-Loan Documents Expired"), _
        Operator:=xlFilterValues
        
    Range("H82").Delete Shift:=xlUp 'Might not want this line anymore - possibly replace with the
        'conditional delete paragraph from above
    
    ActiveSheet.ShowAllData

    lLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").NumberFormat = "0000"
    Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(7, 1)), TrailingMinusNumbers:=True
    Columns("I:I").NumberFormat = "0000"
    Range("A1").FormulaR1C1 = "Identifier"
    
    With Range("A2:A" & lLastDataRow)
        .FormulaR1C1 = "=TEXT(RC[8],""0000"")&""""&RC[3]"
        .Value = .Value
    End With

    Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    
    Range("D11").Select
    
End Sub
 
Upvote 0
Thank you for your replying and cleaning up the code. How would I go about assiging the a name to a Command button?
 
Upvote 0
In the Developer tab, select Design Mode

If you use a forms control button
When you insert the button, you are presented a list of macros in all open workbooks. Find and select the LoanData macro in this workbook.
Click OK


If you used an ActiveX command button
Double-Click on the button, the VBE environment will open and something like this will appear

Private Sub CommandButton1_Click()

End Sub

Edit it to show

Private Sub CommandButton1_Click()
LoanData
End Sub

save the workbook and close the VBE

Exit Design mode
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Macro to Keep Sheet Names as Sheet1 and Sheet2
2
Replies
14
Views
1K
djmnon
D
An Quala
  • Question Question
Should I name it Active.Sheet or sheet name?
Replies
8
Views
893
MARK858
MARK858
M
  • Question Question
VBS Crash
Replies
1
Views
748
Alex Blakenburg
A
Z
  • Question Question
Macro to copy formula down.
2
Replies
11
Views
2K
Fluff
Fluff
D
  • Question Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
944
davidepstein22
D

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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