Paste:=xlColumnWidths: PasteSpecial method of Range class failed

    My code was created directly from the macro recorder. I went back and cleaned a few things up, but now it keeps getting stuck on the column width paste. I created a bunch of templates on worksheet "WATERAB" and I'm trying to copy each template to a new sheet.


    Code
    Sheets.Add
Sheets("WATERAB").Select
Range("DE10:DO21").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Re: column width paste not working


    Not sure exactly what you are trying to do, but for example, this copies a range and then pastes the column widths into another range ("A1").


    Code
    Sheets("Sheet2").Range("D4:L26").Copy
    Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


    You need to select a CELL as a range before you use the PasteSpecial, and not an entire worksheet, because the "paste special" wont really know where to start pasting.... if you know what I mean....


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: column width paste not working


    ok, I tried adding a range and it still is giving me the error:
    "PasteSpecial method of Range class failed"


    Here is the new code:

    Code
    Sheets.Add
Sheets("WATERAB").Range("DE10:DO21").Copy
Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlAll, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, 
_SkipBlanks:=False, Transpose:=False

    Re: column width paste not working


    Which PasteSpecial is failing....? Or why do you have two....?


    I just tried the exact snip of code as you have it there and it works fine....


    The only thing I canged was


    Sheets("WATERAB").Range("DE10:DO21").Copy


    to


    Sheets("Sheets2").Range("DE10:DO21").Copy


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

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

Register Yourself Login