ActiveCell.Offset Help.

[Rilude]

Hello,

I'm hoping that you could help me a little;

I'm trying to copy the data from one worksheet(sheet2) to another (sheet1) using ActiveCell.Offset.

I've done some searching and not really found anything helpful other than actually using something like;

Range(“B2”).Select
Selection.Copy

But I hear that ActiveCell can do everything in one line.

My 'Code';

Sub Test()
Dim Master As Worksheet
Dim Template As Worksheet

Set Master = Sheet1
Set Template = Sheet2

Master.Activate
Range("A1").Select

Template.Activate
Range("A1").Select

AddData:

ActiveCell.Offset(0, 15).Value = ActiveCell.Offset(0, 0).Value
ActiveCell.Offset(0, 18).Value = ActiveCell.Offset(0, 1).Value


End Sub


Can someone point me in the right direction?

Many thanks,

.R

 

[Norie]

Your code is not going to copy values between sheets, it's going to copy values within the same sheet.

ActiveCell refers to the active cell on the active sheet.

What cells do you want to copy?

 

[dphelps526]

I just opened two new workbooks - Book1 and Book2

In this case, lets say I have some data in Book2 A1 that I would like to copy to Book2 A1.

For the following code, it does not matter which workbook, worksheet, or cell is active:

Option Explicit


Sub copy()


    Dim wsBook1 As Worksheet
    Dim wsBook2 As Worksheet
    
    Set wsBook1 = Workbooks("Book1").Worksheets("Sheet1")
    Set wsBook2 = Workbooks("Book2").Worksheets("Sheet1")
    
    wsBook1.Range("A1") = wsBook2.Range("A1").Value


End Sub

 

[dphelps526]

Actually, I misread your question. You are moving from worksheet to worksheet. This small change should do it:

Option Explicit


Sub copy()


    Dim wsSheet1 As Worksheet
    Dim wsSheet2 As Worksheet
    
    Set wsSheet1 = Workbooks("Book1").Worksheets("Sheet1")
    Set wsSheet2 = Workbooks("Book1").Worksheets("Sheet2")
    
    wsSheet1.Range("A1") = wsSheet2.Range("A1").Value


End Sub

 

[Rilude]

Actually, I misread your question. You are moving from worksheet to worksheet. This small change should do it:

Option Explicit


Sub copy()


    Dim wsSheet1 As Worksheet
    Dim wsSheet2 As Worksheet
    
    Set wsSheet1 = Workbooks("Book1").Worksheets("Sheet1")
    Set wsSheet2 = Workbooks("Book1").Worksheets("Sheet2")
    
    wsSheet1.Range("A1") = wsSheet2.Range("A1").Value


End Sub

Hello,

thanks for the replies,

The above will work but I'm not coping the data from the whole sheet, just selected cells - which is why I thought that acticecell.offset might work.

I'll have a play around today,

.R

 

[Rilude]

So. I think I have it if someone could take a once over at my 'code' :D

Sub Import()
'
' Import
'
Dim Template As Worksheet
Dim Master As Worksheet

Set Template = Sheet2
Set Master = Sheet1


Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

' Remove filters and unhide from Master

Master.Activate

Master.Range("A2").Activate
If ActiveSheet.AutoFilterMode Then Cells.AutoFilter

Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

'
Template.Activate
Template.Range("B4").Activate

Dim FindCell As Range
Set FindCell = ActiveCell
FindCell.Activate

Dim Value As Range
Set Value = Selection

Master.Activate

Top:
If ActiveCell = "" Then
GoTo AddData
Else
ActiveCell.Offset(1, 0).Activate
GoTo Top
End If
AddData:
Dim FoundCell As Range
Set FoundCell = ActiveCell

FoundCell.Activate
FoundCell.Offset(0, 0).Value = UCase(FindCell.Offset(0, 0).Value) ' Title1
FoundCell.Offset(0, 1).Value = Format(FindCell.Offset(0, 1).Value, "dd/mm/yyyy") ' Title2
FoundCell.Offset(0, 2).Value = UCase(FindCell.Offset(3, 1).Value) ' Title3
' FoundCell.Offset(0, 0).Value = UCase(FindCell.Offset(0, 3).Value) ' Title4
' FoundCell.Offset(0, 0).Value = UCase(FindCell.Offset(0, 4).Value) ' Title5
' FoundCell.Offset(0, 0).Value = UCase(FindCell.Offset(0, 5).Value) ' Title6
' FoundCell.Offset(0, 0).Value = UCase(FindCell.Offset(0, 6).Value) ' Title7

Template.Activate
NotFound:
ActiveWindow.Zoom = 95
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True

End Sub

 

[dphelps526]

The code that I have won't copy the whole sheet. I'm pretty much giving names to the different sheets so I can go between the two. If you know the cell address, then you can replace the "A1" with whatever location and destination you want.

The code above simply takes the contents of "A1" from one sheet and copies it to "A1" to the other sheet.

 

[dphelps526]

If I'm following your active cells and offsets correctly, this should do the trick...

Option Explicit


Sub copy()


    Dim wsSheet1 As Worksheet
    Dim wsSheet2 As Worksheet
    
    Set wsSheet1 = Workbooks("Book1").Worksheets("Sheet1")
    Set wsSheet2 = Workbooks("Book1").Worksheets("Sheet2")
    
    wsSheet1.Range("A1") = UCase(wsSheet2.Range("A1").Value)
    wsSheet1.Range("B1") = Format(wsSheet2.Range("B1").Value, "dd/mm/yyyy")
    wsSheet1.Range("C1") = UCase(wsSheet2.Range("B4").Value)
    


End Sub