Changing the ActiveCell

  • Thread starter Thread starter Bruce A. Julseth
  • Start date Start date
B

Bruce A. Julseth

I want to Select and copy several block of data based on where the initial
ActiveCell is locate. The following copys the first block.

Dim StartingCell As String
Dim EndingCell As String
Dim RowOffset As Integer
Dim ColOffset As Integer

RowOffset = 2
ColOffset = 3

StartingCell = ActiveCell.Address
EndingCell = ActiveCell.Offset(RowOffset, ColOffset).Address

Worksheets("Sheet1").Range(StartingCell & ":" & EndingCell).Copy _
Destination:=Worksheets("Sheet2").Range("Jeff")

Now, "Logically" I want to Add RowOffset and ColOffset to StartingCell and
to EndingCell and repeat the above copy:

Worksheets("Sheet1").Range(StartingCell & ":" & EndingCell).Copy _
Destination:=Worksheets("Sheet2").Range("Tom")

How do I do the addition to change the values for Starting and Ending Cells?

Thanks....

Bruce
 
One way:


Assuming that the activecell is on Sheet1 (and assuming you only want to
move down the active sheet, not across by adding colOffset):

With ActiveCell
.Resize(3, 4).Copy Sheets("Sheet2").Range("Jeff")
.Offset(3,0).Resize(3, 4).Copy Sheets("Sheet2").Range("Tom")
End With

or

With ActiveCell.Resize(3, 4)
.Copy Sheets("Sheet2").Range("Jeff")
.Offset(3,0).Copy Sheets("Sheet2").Range("Tom")
End With
 
Back
Top