Code Error When run via a Click button

  • Hi All

    I am totally stumped.

    I am using the following code

    which when i run direct from the module - i get the correct data -

    However when i run from a Button it seem to start fail from (marked with an astrix)

    It looks like the code starts getting the info from the sheet I past too, rather than the sheet I copy from.

    Sadly the workbook is far too large to attach (the Source workbook is 820kb) and the workbook with the code is 195kb

    There is other code after the end with, which all works correctly

  • Re: Code Error When run via a Click button

    Weird one that, but solved.

    I Activeated the cell before copying, and the obviously re-working the offset.

    Code looks like this:

    If anyonce can suggest better and tidier code I would appreciate it.

    Thanks again

  • Re: Code Error When run via a Click button

    All those Activates are not very efficient and will slow down your code, and may well be causing your problems, e.g.

    ActiveCell.Offset(1, 0).Activate 
        ActiveCell.Offset(18, 1).Activate 
        cell.Offset(0, 8).PasteSpecial xlPasteValues

    could be replaced by

    ActiveCell.Offset(19, 1).Copy 
        cell.Offset(0, 8).PasteSpecial xlPasteValues

    You could use Find to replace your Do loop.

  • Re: Code Error When run via a Click button

    Thankyou for your response. I was originally doing

             cell.offset(0,4).pastespecial xlpastevalues

    but without activating, after a few the code appeared to be taking the data from the sheet to copy to, rather than the sheet to copy from. (oddly, only when run via a click button).

    I know the new version is slower, but currently seems to work.

    Forgive me, how would you run a Find, to replace the Do Loop? I use Do Loop quite a lot in my codes.


  • Re: Code Error When run via a Click button


    how would you run a Find, to replace the Do Loop

    Something like this. Have also used looping an array to read the various offsets and all worksheet references are qualified so it should not matter what is activated when the data is copied/pasted (But do not know how you defined 'Cell' - check that)

    Obviously this was done outside the context of your workbook so if you want to test it, use a copy - I cannot know the exact structure/layout so is an example only.

    EDIT: Just seen the expanded code in post #1.

    Would strongly suggest that setting the references to the Range variables be changed.

    Set rng = ActiveWorkbook.Sheets("Selection").Range("N2:N11") 
    Dim FRng As Range 
    Set FRng = ActiveWorkbook.Sheets("Selection").Range("AA2:AE11")

    Try to avoid the use of ActiveWorkbook and ActiveSheet. These are rather vague and really should only be used when you are 100% certain the Active workbook/sheet is actually the one you want to refer to.

    Instead of ActiveWorkbook use ThisWorkbook to refer to the workbook the code is running in, or explicitly name the workbook you want to refer to.

    Instead of ActiveSheet, use the sheet name and, preferably, the sheet CodeName as this cannot be changed by the user outside the VBA development environment.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!