Copy values from same row on multiple worksheets to one worksheet

1
votes

I have a code from this similar question: copy the same row from multiple sheets into one sheet in excel

The second answer (from Gary's Student) features the code, and it works perfectly for most of my data. The code in question will take the same row from each worksheet in an excel file, and put all of those rows into the last worksheet.

Problem is, some of my data is made of references. When I run the code, I don't want to copy over "= B12", but I want the paste special value. Can anyone edit the code to work like this?

1
vbaexcel
You should be able to use the PasteSpecial method to paste the cell values instead of formulas: msdn.microsoft.com/en-us/vba/excel-vba/articles/…. Can you post the code you have so far? – Socii

1 Answers

3
votes

adding the .value to the rows you are interested in should solve your reference issue. 

Sub row_copy()

For i = 1 To Worksheets.Count - 1

   Sheets(6).Rows(i) = Sheets(i).Rows(7).Value 'added .value and reversed order of that code

Next i

End Sub

Edit

The second answer

Sub copyrow()
Dim Nrow As Long, Nsheet As Long
Dim i As Long

Nrow = 7
Nsheet = 6

For i = 1 To Nsheet - 1
    Sheets(Nsheet).Rows(i) = Sheets(i).Rows(7).Value
    'Sheets(i).Cells(Nrow, 1).EntireRow.Copy Sheets(Nsheet).Cells(i, 1)
Next i
End Sub

A way to be sure and get every sheet except the sheet you are writing to.

Sub row_copy()
Dim i As Long
i = 1

With Application.Sheets("Your Sheet Name")

    For Each Item In Worksheets

        If Item.Name <> .Name Then
            .Rows(i) = Sheets(i).Rows(7).Value
            i = i + 1
        End If

    Next

End With

End Sub