Cannot Run Recorded Code

  • Hi All,


    I'm having problems running the following code, which was generated via Recording (Tools > Macro > Record New Macro).


    Code
    Sub CopyWidths()
        Sheets("Sheet2").Select
        Range("A1:G1").Select
        Selection.Copy
        Sheets("Sheet1").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End Sub


    The objective of the macro is to apply the column widths of columns A thru G in Sheet2 to the same columns in Sheet1.


    During recording, I am able to complete the procedure without any problems. However, when I try to run the code, I get the following error.


    Quote


    Paste Special Method of Range Class Failed


    Any ideas why this happens?


    m

  • Re: Cannot Run Recorded Code



    xlPateColumnWidths instead of xlColumnWidths
    also add
    ActiveSheet.Paste before selection paste
    and after
    I tried it my end and it works

  • Re: Cannot Run Recorded Code


    Thanks for your reply, wkalo.


    I copy-pasted your code as-is into VBE, but am still getting the same error.


    (Maybe it's because we're running different versions of MS Office.)


    BTW, I'm trying to paste only column widths. But ActiveSheet.Paste will paste other attributes as well (formulas, formats, values, ...).


    m

  • Re: Cannot Run Recorded Code


    Try


  • Re: Cannot Run Recorded Code


    Thanks Ivan. Your code works.


    My first question is: What's the role of the Const declaration in this case?


    Sencondly, is there a way to modify the declaration for a source range that's variable?


    For example, if instead of


    Code
    Sheets("Sheet2").Select 
        Range("A1:G1").Select 
        Selection.Copy


    I were to use


    Code
    Sheets("Sheet2").Range("A1", _
                  Sheets("Sheet2").Range("A1").End(xlToRight)).Copy


    How should I change the Const declaration to accommodate this kind of variable range reference?


    Thanks


    m

  • Re: Cannot Run Recorded Code



    The use of the Const is due to a bug for this particular property of the Paste class. (ie the pasteColumnWidth) Defining it just circumvents the bug.


    Can't find the MS link for the bug ... sorry.....

Participate now!

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