Code works great, except needs to set values not formula

Private Sub CommandButton1_Click()
Dim rCell As Range
Dim i As Long
Dim rNext As Range
'loop through the cells in column A of the source sheet
For Each rCell In Sheet1.Range("A3:U25")
    'loop as many times as the value in column U of the source sheet
    For i = 1 To rCell.Offset(0, 22).Value
        'find the next empty cell to writ开发者_高级运维e to in the dest sheet
        Set rNext = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
        'copy A and B from source to the dest sheet
        rCell.Resize(1, 22).Copy rNext.Resize(1, 1)

        Next i
    Next rCell
End Sub

Ok this works great except how do I copy the values not formulas of cells in sheet1 to sheet2? Like a date transfers as 1/0/1900, when it needs to be 5/5/2011

You need to use the PasteSpecial method with the xlPasteValues as the PasteType. Something like:

Sheet2.Cells(1,1).PasteSpecial xlPasteType.xlPasteValues

Private Sub CommandButton1_Click()
Dim rCell As Range
Dim i As Long
Dim rNext As Range
'loop through the cells in column A of the source sheet
For Each rCell In Sheet4.Range("A3:U25")
    'loop as many times as the value in column U of the source sheet
    For i = 1 To rCell.Offset(0, 23).Value
        'find the next empty cell to write to in the dest sheet
        Set rNext = Sheet12.Cells(Sheet12.Rows.Count, 1).End(xlUp).Offset(1, 0)
        'copy A and B from source to the dest sheet
        rCell.Resize(1, 23).Copy
        rNext.Resize(1, 1).PasteSpecial (xlPasteValues)
    Next i
Next rCell
End Sub

Now I'm getting a runtime-13 type mismatch on below part of the code. When it errors, click end and it works fine. Don't want to have to click end. For i = 1 To rCell.Offset(0, 23).Value