VBA copying cell value from one sheet to another

0
votes

does anyone has a clue how I can solve the following. I have a table in sheet 1 which I want to copy to sheet 2. However, copying will only take place for those cells in sheet1 for which column 3 row 11 to 32 are not empty. If say C11 is not empty, C11 will be copied together E11,F11,H11,I11 and J11. Then the code checks if C12 is filled and does the same and so on.

The copied cells are placed in sheet2 starting row and column 15. I have the following code which works very well. However it copies the formulas which obviously make no sense in sheet2, hence, values are nonsense:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim a As Integer
a = 15
For i = 11 To 32
If Worksheets(1).Cells(i, 3) <> "" Then
Worksheets(1).Cells(i, 3).Copy Worksheets(2).Cells(a, 15)
Worksheets(1).Cells(i, 5).Copy Worksheets(2).Cells(a, 17)
Worksheets(1).Cells(i, 6).Copy Worksheets(2).Cells(a, 18)
Worksheets(1).Cells(i, 7).Copy Worksheets(2).Cells(a, 19)
Worksheets(1).Cells(i, 8).Copy Worksheets(2).Cells(a, 20)
Worksheets(1).Cells(i, 9).Copy Worksheets(2).Cells(a, 21)
a = a + 1
End If
Next i

How can I adjust the code in order to copy only the values?

Thank you very much for any support.

2
excelvba
.Cells(y, x) represents a Range in VBA, and Range has a .PasteSpecial method. (Here's an MSDN link: msdn.microsoft.com/en-us/library/office/…) The option you're probably looking for here is xlPasteValues – Dan Wagner
This question appears to be off-topic because it could be answered using the macro recorder and copy/paste values only option. – David Zemens

2 Answers

1
votes

So, the way to do this with minimal changes to your code would be:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim a As Integer
a = 15
For i = 11 To 32
If Worksheets(1).Cells(i, 3) <> "" Then
Worksheets(2).Cells(a, 15) = Worksheets(1).Cells(i, 3).Value
Worksheets(2).Cells(a, 17) = Worksheets(1).Cells(i, 5).Value
Worksheets(2).Cells(a, 18) = Worksheets(1).Cells(i, 6).Value
Worksheets(2).Cells(a, 19) = Worksheets(1).Cells(i, 7).Value
Worksheets(2).Cells(a, 20) = Worksheets(1).Cells(i, 8).Value
Worksheets(2).Cells(a, 21) = Worksheets(1).Cells(i, 9).Value
a = a + 1
End If
Next i
1
votes
    Dim i As Integer
    Dim rw As Range, rwD as Range

    Set rwD = Worksheets(2).Rows(15)

    For i = 11 To 32
        Set rw = Worksheets(1).Rows(i)
        If rw.Cells(3) <> "" Then

        rwD.Cells(15).Value = rw.Cells(3).Value
        rwD.Cells(17).Value = rw.Cells(5).Value
        rwD.Cells(18).Value = rw.Cells(6).Value
        rwD.Cells(19).Value = rw.Cells(7).Value
        rwD.Cells(20).Value = rw.Cells(8).Value
        rwD.Cells(21).Value = rw.Cells(9).Value

        Set rwD = rwD.Offset(1, 0)
        End If

    Next i