CODEWITHSUNDEEP
vbaexcel
Tefalpan
Tefalpan

Reputation: 94

Excel copy cell values to other worksheet

I'm working on a macro to copy certain rows (if column A isn't blank) of worksheet 'A' to worksheet 'B'. After a little bit of research the following code suddenly appeared. Only thing that I don't seem to work out is to copy the cell values instead of the linked formula, I tried to implement the 'copy/paste special' command, but I don't get the specific coherent code language.

Sub Samenvattend()
'
' Samenvattend Macro
'
' Sneltoets: Ctrl+Shift+S
'

Dim a As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("gedetailleerde meetstaat")
Set Target = ActiveWorkbook.Worksheets("samenvattende meetstaat")

j = 1     ' Start copying to row 1 in target sheet
For Each a In Source.Range("A1:A10000")   ' Do 10000 rows
    If a <> "" Then
       Source.Rows(a.Row).Copy Target.Rows(j)

       j = j + 1
    End If
Next a
End Sub

Thanks :)

Upvotes: 0

Views: 142

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

If you're just trying to set the values of two cells equal, you can skip copy/paste, and simply set the ranges' values equal to one another.

This also lets you skip using the Clipboard and tends to be a little faster.

Just remember when doing this, it's [DESTINATION range].value = [ORIGIN range].value whereas with copy/paste, it's [ORIGIN range].copy [DESTINATION range].

For Each a In Source.Range("A1:A10000")   ' Do 10000 rows
    If a <> "" Then
       Target.Rows(j).value = Source.Rows(a.Row).Value
       j = j + 1
    End If
Next a

Upvotes: 3

Related Questions