Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Macro works but will not work when run as VBA

  1. #1
    Join Date
    5th January 2011
    Posts
    21

    Macro works but will not work when run as VBA

    I have a macro created that works fine. However, when I try to get it to run from an ActiveX button, it does not work at all.

    Is there a certain mode or option I have to toggle to get it to work?

    Macro is as follows:
    VB:
    Sub Combine() 
         '
         ' Combine Macro
         '
         '
        Application.ScreenUpdating = False 
         
         'Clears current list
        Sheets("Sheet3").Select 
        Columns("J:J").Select 
        Selection.ClearContents 
         'Copy paste from other sheets
        Sheets("Sheet2").Select 
        Range("C2:C1500").Select 
        Selection.Copy 
        Sheets("Sheet3").Select 
        Range("J2").Select 
        ActiveSheet.Paste 
        Sheets("Sheet1").Select 
        Range("C2:C1500").Select 
        Application.CutCopyMode = False 
        Selection.Copy 
         'Selects first empty row
        Sheets("Sheet3").Select 
        Range("J2").Select 
        Do 
            If IsEmpty(ActiveCell) = False Then 
                ActiveCell.Offset(1, 0).Select 
            End If 
        Loop Until IsEmpty(ActiveCell) = True 
         
        ActiveSheet.Paste 
         
         
         'Removes duplicates and sorts column
        Columns("J:J").Select 
        Application.CutCopyMode = False 
        ActiveSheet.Range("$J$1:$J$3157").RemoveDuplicates Columns:=1, Header:=xlNo 
        ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear 
        ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("J1:J3157") _ 
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 
        With ActiveWorkbook.Worksheets("Sheet3").Sort 
            .SetRange Range("J1:J3157") 
            .Header = xlGuess 
            .MatchCase = False 
            .Orientation = xlTopToBottom 
            .SortMethod = xlPinYin 
            .Apply 
        End With 
    End Sub 
    
    
    Thanks in advance,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: Macro works but will not work when run as VBA

    Hi Emerald

    Lets start by cleaning your code up a bit. I am not sure what the sort at the bottom is trying to achieve. THe attached file assumes no headers.

    Take care

    Smallman

    VB:
    Option Explicit 
    Private Sub CommandButton1_Click() 
        Application.ScreenUpdating = False 
         
         'Clears current list
        Sheets("Sheet3").Range("J:J").ClearContents 
        Sheets("Sheet2").Range("C2:C1500").Copy Sheets("Sheet3").Range("J2") 
        Sheets("Sheet1").Range("C2:C1500").Copy Sheets("Sheet3").Range("J" & Rows.Count).End(xlUp)(2) 
         'Removes duplicates and sorts column
        Sheets("Sheet3").Range("J1", Range("J" & Rows.Count).End(xlUp)).RemoveDuplicates 1, xlNo 
        Sheets("Sheet3").Range("J2", Range("J" & Rows.Count).End(xlUp)).Sort Columns(10), xlAscending 
         
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th April 2012
    Location
    Florida
    Posts
    6

    Re: Macro works but will not work when run as VBA

    You cleaned it up nicely Smallman :-)
    Emerald, the code is not working because the code was not linked to the "click" event of the button. (Just in case you hadn't figured it out from Smallman's code)

    Greetings,
    Apcbr

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    5th January 2011
    Posts
    21

    Re: Macro works but will not work when run as VBA

    Thank you very much Smallman!

    @Apcbr - I had tried putting the macro code under a button click Sub, but it would give me "Select method of Range class failed" error.

    I was getting the same error using Smallman's code also. But, thanks to the example spreadsheet he attached, I saw that the only difference between my sheet and his was that on mine, the button was on Sheet2. On his, it is on Sheet3.

    Moving the button to Sheet3 seems to be working well

    Thanks again!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: Macro works but will not work when run as VBA

    Hi Emerald

    Thanks glad you got the procedure working. In future it is a good idea to attach a sample of your workbook that way there is no confusion.

    Acpbr - the code is linked to the click event of the button, in contrast to your informed comment. The code runs so quickly it appears nothing is happening. However go into the file, clear the range on sheet3 click the button. Works doesn't it!!!

    Take it easy

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th April 2012
    Location
    Florida
    Posts
    6

    Re: Macro works but will not work when run as VBA

    Yes it does Smallman,

    I was referring to Emeralds code that did not seem to be linked to the click event

    Cheers,
    Apcbr

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: Macro works but will not work when run as VBA

    Oh Ok I am sorry then. I thought you were saying the code didn't work. I misunderstood. This time my bad.

    Take it easy

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,641

    Re: Macro works but will not work when run as VBA

    This should work even the button is in Sheet2
    VB:
    Sub Combine() 
        Application.ScreenUpdating = False 
        With Sheets("Sheet3") 
             'Clears current list
            .Columns("J").ClearContents 
             'Copy paste from other sheets
            Sheets("Sheet2").Range("C2:C1500").Copy .Range("J2") 
            Sheets("Sheet1").Range("C2:C1500").Copy .Range("j" & Rows.Count).End(xlUp)(2) 
            .Range("$J$1:$J$3157").RemoveDuplicates Columns:=1, Header:=xlNo 
            .Range("J2:J3157").Sort .Range("J2"), 1 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: June 1st, 2011, 23:52
  2. Code works then doesn't work!
    By iwrk4dedpr in forum EXCEL HELP
    Replies: 15
    Last Post: September 17th, 2004, 10:27
  3. Code works / code doesnt work
    By stevehorton09 in forum EXCEL HELP
    Replies: 3
    Last Post: August 20th, 2004, 19:38
  4. Macro won't work first time it's launched but work
    By Bronsted in forum EXCEL HELP
    Replies: 5
    Last Post: August 13th, 2003, 04:31

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno