VBA- copy and paste

[Nani520]

Hi. I'm just new to VBA and I'm just wondering if someone can help me in writing a macro code for below:

If in Column A contains the word "Value", it would be replaced by the cell's value (Acc) in sheet 2.

Thank you in advance.

Sheet1

Sheet2

 

[Nani520]

When involves another worksheet or even another workbook, it is easier when you create reference. This way you don't have to use Select command.

Here is example

Sub Test()

Dim n As Long
Dim cell As Range, rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set rngData = ws1.Range("A1", ws1.Cells(Rows.Count, "A").End(xlUp))

n = 1
For Each cell In rngData
    If cell = "Value" Then
        n = n + 1
        cell = ws2.Range("F" & n)
    End If
Next

End Sub

It works perfectly now, Thank you so much.