none
Paste Special values and color of cells RRS feed

  • Question

  • Hi:

      I am not a professional.  Trying to copy from one sheet and use Paste Special Values but also want to paste the color of each cell from sheet 1 to sheet 2 without disturbing any other formatting of sheet 2.

      Code used for Paste Special Values is:

       Range("A1").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False

      Is it possible to add PasteSpecial for only the colors of each of the cells as well as the values?

      Thanks, Don

    • Moved by Fred Bao Monday, March 9, 2015 6:27 AM Office Related
    Monday, March 9, 2015 4:26 AM

Answers

  • PasteSpecial > Formats

    Sub Macro1()

        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, March 9, 2015 6:36 PM

All replies

  • Hello Don,

    Since this issue is related with Excel, i move it to the Excel forum there are Excel experts will help you. The current forum you posted to is used to discuss and ask questions about .NET Framework Base Classes (BCL) such as Collections, I/O, Regigistry, Globalization, Reflection.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 9, 2015 6:25 AM
  • Hi Don,

    As far as I know there is no pastespecial command in Excel to paste only colors, rather it allows for full format paste. The way you have your VBA setup you are limited to whatever built in paste functionality Excel has.

    The other way to accomplish this would be in a loop and then to use the Cell(1,1).Interior.Color parameter to determine the color code of each cell and set the other sheet's cell to the same color code.

    If someone has another method then please let me know as well!

    Monday, March 9, 2015 6:23 PM
  • PasteSpecial > Formats

    Sub Macro1()

        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, March 9, 2015 6:36 PM
  • Thank you for your response.  I believe you are correct.  Colors would be included along with the other formatting criterion.

    Appreciate your help,

      Don

    Friday, March 13, 2015 2:13 AM