Macro paste special - Compile Error

    Hi All

    Thanks to date for all the previous assistance has really hepled me in understanding more about Macros and working through shortening what was very long winded Macros.

    The issue I am having is selecting a range of cells with formulas and hard coding them.

    Code
    Sub PASTE_special_FORMULAS()
Range("E8:P18,E22:P32,E36:P46,E50:P60,E64:P74,E78:P88").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
End Sub



    Is there a way of pasting values for ranges without having to have a macro for each range

    Thanks again

    Lee

    Re: Macro paste special - Compile Error


    Lee,

    Please take note of the correct addition of [noparse]

    Code

    [/noparse] tags - see the link in my signature for more details of using tags. Also, please revise your title (by using Edit Post>Go Advanced) to better represent the actual problem you are experiencing. Your title describes a compile error but the description of your problem does not.

    Thank you.

    Re: Macro paste special - Compile Error


    AFAIK, you cant use PasteSpecial across non-contiguous rows and columns. If you use paste special I believe the cells being copied have to be in


    a) all appearing in one row (can be contiguous or non-contiguous)
    b) all appearing in one column (can be contiguous or non-contiguous)
    c) if across several rows & columns, the cells need to be coniguous - i.e. together in one block, not several blocks like you have.


    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: Macro paste special - Compile Error


    Try this instead:

    Code
    Sub PASTE_special_FORMULAS()
Dim my_range As Range
Dim r As Range
Set my_range = Range("E8:P18,E22:P32,E36:P46,E50:P60,E64:P74,E78:P88")
For Each r In my_range.Areas
    r.Copy
    r.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next
 
End Sub


    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: Macro paste special - Compile Error


    Lee,


    Given that you are not attempting to paste the values in a different location, only overwrite formulas with values, try this:

    Code
    With Range("E8:P18,E22:P32,E36:P46,E50:P60,E64:P74,E78:P88")
    .Value = .Value
End With


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Macro paste special - Compile Error


    Hi Rob

    Can you assist me with the codes.

    This is what I do

    CODE Start[ paste code] Code finish.

    Sorry this is what I thought I was meant to do from reading the instructions.

    Lee

    Re: Macro paste special - Compile Error


    Hi Batman

    With your code on the last column "P" it puts the value as #NA whereas if I copy and paste manually it is zero.

    I like this simple code so if you can assist with fixing I would greatly appreciate.


    Thanks
    Lee

    Re: Macro paste special - Compile Error


    Hi L7B



    You need to use square brackets to show that it is actually a TAG.
    Use [CODE] Your Code [/ CODE] (with no space between the / and CODE]


    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: Macro paste special - Compile Error


    Lee,


    Before running the macro, do your formulas in column P return an error? The macro should only overwrite the formulas with whatever was the result of those formulas. What was the formula before running the macro, and what values were showing?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Macro paste special - Compile Error


    Hi

    No there is no error. I have copied and paste columns on there own and no problems. All the other sheets work fine.

    Thanks
    Lee

    Re: Macro paste special - Compile Error


    It seems I am invisible.


    How have you been Batman? Havent seen you around these parts in a while.


    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: Macro paste special - Compile Error


    Been extremely busy, which is why I haven't been around. Might be only a fleeting visit, as more projects looming on the horizon!

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Macro paste special - Compile Error


    Lee,


    Could you attach a small sample of the data that doesn't work with the macro?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

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

Register Yourself Login