Copy data from different sheet to another sheet using activecell offset

[urubag]

Hi All,

Please note I already have a code that works but I would like to do it faster.

Dim X1, X2 as worksheet

Set X1 = Sheets("DataBase Hierarchy")
Set X2 = Sheets("Tool")

    X1.Select
    ActiveCell.Offset(0, -1).Copy
    X2.Select
    ActiveCell.Offset(0, 1).PasteSpecial xlPasteValues

I was trying to do something like this but not sure if is possible.

Dim X1, X2 as worksheet

Set X1 = Sheets("DataBase Hierarchy")
Set X2 = Sheets("Tool")

X2.ActiveCell.Offset(0, 1).Value = X1.ActiveCell.Offset(0, -1).Value

The idea is to copy the data from activecell offset into another one in activecell Offset.

Appreciate your response.
Regards
Andres

 

[Joe4]

I think the issue is that you can really only have one ActiveCell, and it is dependent upon whatever is the ActiveSheet at that point in time.
So I don't think you can have two ActiveCells at the exact same time on different sheets, because only one of those sheet can be the ActiveSheet at that point in time!

 

[jolivanes]

Dim X1, X2 as worksheet

If you do it like above, X1 is dimensioned as Variant.
In this case, it should be like so

Dim X1 As Worksheet, X2 as Worksheet

Is the 2nd ActiveCell (X1.ActiveCell) meant to be the same address of the first ActiveCell (X2.ActiveCell)?
Or is it meant to be the selected cell in that sheet before you changed sheets?

 

[jolivanes]

Change Sheet references as required

If Sheet2 is your Active Sheet

Sub One_Possibility()
Dim X1 As Worksheet, X2 As Worksheet
Dim ActC As String
Set X1 = Sheets("Sheet1")
Set X2 = Sheets("Sheet2")
ActC = ActiveCell.Address
    ActiveCell.Offset(0, 1).Value = X1.Range(ActC).Offset(0, -1).Value
End Sub

If Sheet1 is your Active Sheet

Sub Another_Possibility()
Dim X1 As Worksheet, X2 As Worksheet
Dim ActC As String
Set X1 = Sheets("Sheet1")
Set X2 = Sheets("Sheet2")
ActC = ActiveCell.Address
    X2.Range(ActC).Offset(0, 1).Value = ActiveCell.Offset(0, -1).Value
End Sub