none
How do I type VBA code to copy range and paste the values??? RRS feed

  • Question

  • Hi,

    I am currently using this code to copy information from one tab to another:

    Private Sub Worksheet_Activate()
    Worksheets("Hunter Residences").Range("B3:B295").Copy Range("B2:B294")
    Worksheets("Hunter Residences").Range("E3:E295").Copy Range("C2:C294")
    Worksheets("Hunter Residences").Range("G3:G295").Copy Range("D2:D294")
    Worksheets("Hunter Residences").Range("H3:H295").Copy Range("E2:E294")
    Worksheets("Hunter Residences").Range("O3:O295").Copy Range("F2:F294")
    Worksheets("Hunter Residences").Range("AK3:AK295").Copy Range("G2:G294")
    End Sub

    The problem is that the original cells for the last line ("AK3:AK295") contain formulas. What do I need to write so that when it pastes the information, it pastes the value??

    Wednesday, August 28, 2019 4:38 AM

Answers

  • First example will only copy the values without formulas. It does not copy the formats (if any).

    Private Sub Worksheet_Activate()

        Range("B2:B294").Value = Worksheets("Hunter Residences").Range("B3:B295").Value
        Range("C2:C294").Value = Worksheets("Hunter Residences").Range("E3:E295").Value
        Range("D2:D294").Value = Worksheets("Hunter Residences").Range("G3:G295").Value
        Range("E2:E294").Value = Worksheets("Hunter Residences").Range("H3:H295").Value
        Range("F2:F294").Value = Worksheets("Hunter Residences").Range("O3:O295").Value
        Range("G2:G294").Value = Worksheets("Hunter Residences").Range("AK3:AK295").Value

    End Sub

    The second example will copy the values and the formats. Note that both the values and the formats can be pasted after the one copy line.

    Private Sub Worksheet_Activate()

        Worksheets("Hunter Residences").Range("B3:B295").Copy
        Range("B2:B294").PasteSpecial xlPasteValues
        Range("B2:B294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("E3:E295").Copy
        Range("C2:C294").PasteSpecial xlPasteValues
        Range("C2:C294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("G3:G295").Copy
        Range("D2:D294").PasteSpecial xlPasteValues
        Range("D2:D294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("H3:H295").Copy
        Range("E2:E294").PasteSpecial xlPasteValues
        Range("E2:E294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("O3:O295").Copy
        Range("F2:F294").PasteSpecial xlPasteValues
        Range("F2:F294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("AK3:AK295").Copy
        Range("G2:G294").PasteSpecial xlPasteValues
        Range("G2:G294").PasteSpecial xlPasteFormats
       
     End Sub


    Regards, OssieMac


    • Edited by OssieMac Wednesday, August 28, 2019 11:36 PM
    • Marked as answer by Brooke86 Thursday, August 29, 2019 12:39 AM
    Wednesday, August 28, 2019 6:50 AM

All replies

  • First example will only copy the values without formulas. It does not copy the formats (if any).

    Private Sub Worksheet_Activate()

        Range("B2:B294").Value = Worksheets("Hunter Residences").Range("B3:B295").Value
        Range("C2:C294").Value = Worksheets("Hunter Residences").Range("E3:E295").Value
        Range("D2:D294").Value = Worksheets("Hunter Residences").Range("G3:G295").Value
        Range("E2:E294").Value = Worksheets("Hunter Residences").Range("H3:H295").Value
        Range("F2:F294").Value = Worksheets("Hunter Residences").Range("O3:O295").Value
        Range("G2:G294").Value = Worksheets("Hunter Residences").Range("AK3:AK295").Value

    End Sub

    The second example will copy the values and the formats. Note that both the values and the formats can be pasted after the one copy line.

    Private Sub Worksheet_Activate()

        Worksheets("Hunter Residences").Range("B3:B295").Copy
        Range("B2:B294").PasteSpecial xlPasteValues
        Range("B2:B294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("E3:E295").Copy
        Range("C2:C294").PasteSpecial xlPasteValues
        Range("C2:C294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("G3:G295").Copy
        Range("D2:D294").PasteSpecial xlPasteValues
        Range("D2:D294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("H3:H295").Copy
        Range("E2:E294").PasteSpecial xlPasteValues
        Range("E2:E294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("O3:O295").Copy
        Range("F2:F294").PasteSpecial xlPasteValues
        Range("F2:F294").PasteSpecial xlPasteFormats
       
        Worksheets("Hunter Residences").Range("AK3:AK295").Copy
        Range("G2:G294").PasteSpecial xlPasteValues
        Range("G2:G294").PasteSpecial xlPasteFormats
       
     End Sub


    Regards, OssieMac


    • Edited by OssieMac Wednesday, August 28, 2019 11:36 PM
    • Marked as answer by Brooke86 Thursday, August 29, 2019 12:39 AM
    Wednesday, August 28, 2019 6:50 AM
  • OssieMac, you are a star. Thank you!!!!
    Wednesday, August 28, 2019 10:19 PM
  • Sorry, just to note, I had to add .value to the last range.
    Wednesday, August 28, 2019 10:22 PM
  • Sorry, just to note, I had to add .value to the last range.

    My apologies for the error. I corrected it in my post.

    I am still looking for a keyboard that types what I meant to type and not what I did (or did not) type.


    Regards, OssieMac

    Wednesday, August 28, 2019 11:38 PM
  • Hahaha, please let me know when you find one :)
    Thursday, August 29, 2019 12:40 AM