CODEWITHSUNDEEP
vbaexcelexcel-formula
acr
acr

Reputation: 1746

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

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.

Upvotes: 0

Views: 1824

Answers (1)

TheEngineer
TheEngineer

Reputation: 1215

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

Upvotes: 1

Related Questions