Copy cell value only with no formatting to destination.

  • I have written this code to copy a range from one sheet to another and it works fine. However, I want to copy only the cell value without any formatting such as cell colour. I've tried a few different ways but it's not working for me. Any help would be great!


    [VB]
    Dim Counter As Integer
    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = FirstTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = FirstTeam And LastCell > 22 And Counter <= 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("CityQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Copy cell value only with no formatting to destination.


    Code
    1. Then Range(FirstCell, LastCell.Offset(0, -1)).Copy
    2. Sheets("CityQualifiers").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial(xlPasteValues)
  • Re: Copy cell value only with no formatting to destination.


    Thank you for your suggestion. I actually tried the same thing but for some reason instead of copying only two rows and adding it to what is already on the sheet, it repeatedly copies the rows specified by the code directly before it to the 2000th row. This is the code directly before it. It simply copies 22 rows from one sheet to the other so in total both pieces of code should copy 24 rows.


    [VB]
    'Copies top 22 runners into CityQualifiers
    Dim TeamRng As Range
    Set TeamRng = Range("A4:C25")
    TeamRng.Select
    Selection.Copy
    Sheets("CityQualifiers").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.