Moving a cell two colums to the right

[Thecraftycarrot]

Hi All, i am trying to copy a cell to columns to the right.

The below code is relating to an if yes statement, that if criteria is met, it will find the the required cell and update it (successfully works)

Dim checkCell As Range
  Dim cell As Range
            Dim endRow As Long
            Dim updateRow As Long
            Dim updateCol As Long
            Dim colName As String
            Dim updateValue As String

Set checkCell = Sheets("Sheet 1 ").Range("G45")

If checkCell.Value = "Yes" Then

rowName = Sheets("Sheet1").Range("M45").Value
updateRow = Sheets("sheet2").Range("A4:a1000").Find(rowName, LookIn:=xlValues).Row
colName = "Persons name"
updateCol = Sheets("sheet2").Range("A3:AZ3").Find(colName, LookIn:=xlValues).Column
updateValue = Sheets("Sheet1").Range("E6").Value
Sheets("sheet2").Cells(updateRow, updateCol).Value = updateValue

However i need to tweek the above formula, instead of the updating the found cell i.e. Sheets("sheet2").Cells(updateRow, updateCol).Value - i need to copy and paste/offset this into a cell on the same row but two columns to the right.

i would imagine the code for updateValue will need to be something like this (but really have no idea how to get this to work):

 updateValue = Sheets("sheet2").Cells(updateRow, updateCol).Value.offset(0,2)

 

[Ramballah]

aint it a simple matter of .copy and then .offset(0.2) ?
* i aint the best in macro's but i have a code for my own sheet where i have to copy and paste things with offset through loops so i was thinking if its just a simple matter of .copy and .paste but with offset like i have in my sheet?

 

[Thecraftycarrot]

i am not sure, never used vba for copy / paste offset etc. could you maybe respond with how you would expect it to work? i.e. a response with some coding. i dont need it to loop though, just copying that one cell.

 

[Thecraftycarrot]

aha! just got it dont worry - code i used was this:
updateValue = Sheets("sheet2").Cells(updateRow, updateCol).Value

Sheets("sheet2").Cells(updateRow, updateCol).offset(0,2).Value = updateValue

 

[Ramballah]

okay i hope it works,
replace Sheets("sheet2").Cells(updateRow, updateCol).Value = updateValue then with:

[COLOR=#333333]Sheets("sheet2").Cells(updateRow, updateCol).Copy
[/COLOR][COLOR=#333333]Sheets("sheet2").Cells(updateRow, updateCol).Offset(0.2)
[/COLOR].PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats

Make sure you use it in a different workbook because u cant undo changes from macro's. Some people forget this and mess up their sheets.