Hi all,

How do you select an Activecell.Offset range? As I am trying to copy
the cells to the left of the ActiveCell.Select.


Code:
--------------------

Range(ActiveCell.Offset(0, 0):ActiveCell.Offset(0, 1)).Select
Selection.Copy

--------------------


Something like the above....?


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=514841

Activecell.Resize(,2).Copy

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"gti_jobert" wrote
in message ...

Hi all,

How do you select an Activecell.Offset range? As I am trying to copy
the cells to the left of the ActiveCell.Select.


Code:
--------------------

Range(ActiveCell.Offset(0, 0):ActiveCell.Offset(0, 1)).Select
Selection.Copy

--------------------


Something like the above....?


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=514841

Hi again,

The code below works fine to delete a row.....but if row columns A an
B contain data then i want to copy the values, delete the row the
paste the new values into the new row. I have highlighted in red th
code that needs to be able to perform this operation, any hel
appreciated!


Code
-------------------

Public Sub DelIndividual()

Dim i%, start%, finish%
Dim s$, f$, r$, flag As Boolean

flag = False
'check for valid cell selection
If (ActiveCell.Column = 3) And (ActiveCell.Value < "") Then
start = ActiveCell.Row
i = start
Do
i = i + 1
Loop Until (Cells(i, 3).Value < "") Or (Cells(i, 7).Value = "") And (Cells(i, 8).Value = "")
finish = i - 1
ActiveSheet.Rows(start).Select
'confirm
r = MsgBox("Do you want to permanently delete this record?", 52, "Delete")
If r = vbYes Then
'delete the whole record
ActiveSheet.Unprotect Password:=Pass

If ActiveCell.Offset(0, 0) < "" And ActiveCell.Offset(0, 1) < "" Then
'copy data and set flag
ActiveCell.Resize(, 2).Copy
flag = True
End If
For i = start To finish
ActiveSheet.Rows(start).Delete
Next

If flag = True Then
ActiveCell.Resize(, 2).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If
ActiveSheet.Protect Password:=Pass
End If
ActiveCell.Offset(0, 0).Select
End If

End Sub


-------------------


Thanks for the code before bob, not too sure how to encorporate i
though

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=51484

In sheet1 with cell (say) B3 the active cell
and with A3 containing the Value 123
Run:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/21/2006 by Jim May
'

'
ActiveCell.Offset(0, -1).Range("A1").Copy
Sheets("Sheet2").Activate
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Sheet2, Cell D1 will now contain the 123.

HTH

"gti_jobert" wrote
in message ...

Hi all,

How do you select an Activecell.Offset range? As I am trying to copy
the cells to the left of the ActiveCell.Select.


Code:
--------------------

Range(ActiveCell.Offset(0, 0):ActiveCell.Offset(0, 1)).Select
Selection.Copy

--------------------


Something like the above....?


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=514841

Code:
--------------------

If ActiveCell.Offset(0, 0) < "" And ActiveCell.Offset(0, 1) < "" Then
'copy data and set flag
ActiveCell.Resize(, 2).Copy
ActiveCell.Resize(1, 2).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If

--------------------


Got this now, but its not pasting the row values into the row below
it....any ideas?

Cheers guys.


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=514841

Code:
--------------------

If ActiveCell.Offset(0, 0) < "" And ActiveCell.Offset(0, 1) < "" Then
'copy data and set flag
ActiveCell.Resize(, 2).Copy
ActiveCell.Resize(*2*, 2).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If

--------------------


Sorted it!!


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=514841