locked
Copy and paste (VALUE ONLY-no formula) entire ROW from one sheet to another RRS feed

  • Question

  • Here is my code so far

    Dim xRg As Range
        Dim xCell As Range
        Dim I As Long
        Dim J As Long
        Dim K As Long
        I = Worksheets("Sheet1").UsedRange.Rows.Count
        J = Worksheets("Sheet3").UsedRange.Rows.Count
        If J = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Sheet3").UsedRange) = 0 Then J = 0
        End If
        Set xRg = Worksheets("Sheet1").Range("O1:O" & I)
        On Error Resume Next
        Application.ScreenUpdating = False
        For K = 1 To xRg.Count
            If CStr(xRg(K).Value) = "YES" Then
                xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & J + 1) 'The code works but I do not want to transfert formulas to sheet 3 'only values
                xRg(K).EntireRow.Delete
                If CStr(xRg(K).Value) = "Done" Then
                    K = K - 1
                End If
                J = J + 1
            End If
        Next
        Application.ScreenUpdating = True
    End Sub

    'Your help would be much appreciated

    'Thanks

    Thursday, January 11, 2018 5:49 PM

Answers

  • Change the line to 2 lines as follows. Note that there is a space between PasteSpecial and Paste and no other delimiter.

    xRg(K).EntireRow.Copy

    Worksheets("Sheet3").Range("A" & J + 1).PasteSpecial Paste:=xlPasteValues


    Regards, OssieMac

    Thursday, January 11, 2018 7:27 PM
  • Please post a copy of your amended code because I think that you have made an error in translating what I have given you. Paste Special Values does exactly what you are requesting. It does not paste the formulas.

    Regards, OssieMac

    • Marked as answer by Cham07 Sunday, January 14, 2018 12:47 AM
    Friday, January 12, 2018 11:56 AM

All replies

  • Change the line to 2 lines as follows. Note that there is a space between PasteSpecial and Paste and no other delimiter.

    xRg(K).EntireRow.Copy

    Worksheets("Sheet3").Range("A" & J + 1).PasteSpecial Paste:=xlPasteValues


    Regards, OssieMac

    Thursday, January 11, 2018 7:27 PM
  • I am afraid I am having the same problem again. WHat I want to achieve is as follow:

    Remove entire row from Sheet 1

    Past those values to Sheet 3(as it was in sheet 1 so current formulas in sheet 1 shouldn't apply and/or be transferred to sheet 3).

    As it appears , with either codes, the formulas are still being transferred from sheet 1 and sheet 3.  

    For example I don't want the formula below (located in column B) to be transfered:

    =IF(OR(A6="",ISBLANK(A6)),"",TEXT(ROW(Dashboard!H33),"000-000"))


    This formula is helping created item numbers in consecutive order (only when A is not blank). I don't want items numbers to change in Sheet 3 for obvious reasons.

    Thanks for your help in this

    Regards,



    Cham07

    Friday, January 12, 2018 9:40 AM
  • Please post a copy of your amended code because I think that you have made an error in translating what I have given you. Paste Special Values does exactly what you are requesting. It does not paste the formulas.

    Regards, OssieMac

    • Marked as answer by Cham07 Sunday, January 14, 2018 12:47 AM
    Friday, January 12, 2018 11:56 AM
  • Sub Cheezy()
    
        Dim xRg As Range
        Dim xCell As Range
        Dim I As Long
        Dim J As Long
        Dim K As Long
        I = Worksheets("Sheet1").UsedRange.Rows.Count
        J = Worksheets("Sheet3").UsedRange.Rows.Count
        If J = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Sheet3").UsedRange) = 0 Then J = 0
        End If
        Set xRg = Worksheets("Sheet1").Range("O1:O" & I)
        On Error Resume Next
        Application.ScreenUpdating = False
        For K = 1 To xRg.Count
            If CStr(xRg(K).Value) = "YES" Then
                xRg(K).EntireRow.Copy
                Worksheets("Sheet3").Range("A" & J + 1).PasteSpecial Paste:=xlPasteValues
                xRg(K).EntireRow.Delete
                If CStr(xRg(K).Value) = "Done" Then
                    K = K - 1
                End If
                J = J + 1
            End If
        Next
        Application.ScreenUpdating = True
    End Sub


    Cham07

    Friday, January 12, 2018 12:28 PM
  • Let me know if you need any further information.

    Many thanks


    Cham07

    Friday, January 12, 2018 6:00 PM
  • There is something else wrong in the code because PasteSpecial Paste:=xlPasteValues does not past the formulas.

    Have you already got the formulas pasted to the range and your intention is to paste over it? If so, comment out the On Error Resume Next and ensure that it is not skipping some code and not even processing the PastSpecial line.

    If you can't find the error then zip the file and upload to OneDrive and I will have a look at it.


    Regards, OssieMac

    Friday, January 12, 2018 7:56 PM
  • It works perfectly. I found the error. A formula on Sheet 3 was generating the error.

    Thanks for your help!




    Cham07

    Sunday, January 14, 2018 12:46 AM