Copy and paste data from one a sheet to another sheet, and from second sheet to third using excel VBA

0
votes

I have requirement, where I need to copy values(not formula) in a sheet cells part of a range to corresponding cells in another sheet and then from second sheet to corresponding cells in third sheet.

for copying data from first two second I am using below code,

Sub Test()

    Dim rng         As Range
    Dim c           As Range
    Dim dest        As Worksheet


    Set rng = Range("C3:E3")
    Set dest = ThisWorkbook.Worksheets("Sheet2")

        For Each c In rng

        c.Copy dest.Cells(c.Row, c.Column)

    Next c

End Sub

Now, I would like to copy from sheet2 to sheet3. I need this specifically from sheet2, because my first sheet may have different value than sheet2.

1
vbaexcelexcel-formula
Instead of using Copy, you can do this dest.Cells(c.Row, c.Column).Value = c.Value – Tim Williams
@Tim Williams Copy used because the cells may have different data later and I need to keep them as separate – acr
You say "I need to copy values(not formula)", so it's not clear why you're using Copy, unless you mean the cells you're copying have no formulas? – Tim Williams
@ Tim Williams In beginning, these cells don't have any formula, but I will be one in first sheet, so the data in first sheet will get change. I need those three sheet after that to compare the values and perform different functions.. – acr
.Value = .Value only changes the value when the macro runs. Your issue with changing references doesn't apply @acr – Chrismas007

1 Answers

1
votes

If you need Sheet1 to contain the formulas, but you need Sheet2 and Sheet3 to contain just the values, modify your code to the following:

Sub Test()

Dim rng         As Range
Dim c           As Range
Dim dest        As Worksheet
Dim dest2       As Worksheet


Set rng = Range("C3:E3")
Set dest = ThisWorkbook.Worksheets("Sheet2")
Set dest2 = ThisWorkbook.Worksheets("Sheet3")

For Each c In rng
    c.Copy
    dest.Cells(c.Row, c.Column).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    dest2.Cells(c.Row, c.Column).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next c

Application.CutCopyMode = False

End Sub