How do I edit a selected range then copy the range into an new sheet???
 

Hi,

I have a spreadsheet that contains a list of cheques that I have poste
to clients. When these cheques are cashed or cancelled I highlight th
selected line in a colour and type cashed or cancelled in column J.
In a seperate sheet I then create a list of all the cheques lines tha
I have edited.

What I would like is a macro that does this for me: I.e highlights th
current line and turns the font colour purple, types cancelled i
column J and copies the contents of cell D to the bottom of the list o
the updates sheet.

I've been experienmenting on my own but can't get excel to copy th
contents correctly.

Thanks

For your help. :)


--
dwybor
-----------------------------------------------------------------------
dwyborn's Profile: http://www.excelforum.com/member.php...fo&userid=2969
View this thread: http://www.excelforum.com/showthread.php?threadid=49410

How do I edit a selected range then copy the range into an new sheet???
 

Sub CancelCheque()
Dim rng as Range, rng1 as Range
set rng = ActiveCell.entireRow
rng.font.ColorIndex = 39
set rng1 = worksheets("Cancelled").Cells( _
rows.count,"j").end(xlup).offset(1,-9)
cells(rng.row,"j").Value = "Cancelled"
rng.copy
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
End Sub

if you wanted to do it for multiplecells. select one cell in each of the
rows and then do (this is a more generalized approach and will work for a
single cell as well)

Sub CancelCheques()
Dim rng as Range, rng1 as Range
Dim rng2 as Range, cell as Range
set rng2 = Intersect(selection.EntireRow,Columns(1))
for each cell in rng2
set rng = cell.EntireRow
rng.font.ColorIndex = 39
set rng1 = worksheets("Cancelled").Cells( _
rows.count,"j").end(xlup).offset(1,-9)
cells(rng.row,"j").Value = "Cancelled"
rng.copy
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
Next
End sub

--
Regards,
Tom Ogilvy

"dwyborn" wrote in
message ...

Hi,

I have a spreadsheet that contains a list of cheques that I have posted
to clients. When these cheques are cashed or cancelled I highlight the
selected line in a colour and type cashed or cancelled in column J.
In a seperate sheet I then create a list of all the cheques lines that
I have edited.

What I would like is a macro that does this for me: I.e highlights the
current line and turns the font colour purple, types cancelled in
column J and copies the contents of cell D to the bottom of the list on
the updates sheet.

I've been experienmenting on my own but can't get excel to copy the
contents correctly.

Thanks

For your help. :)
D


--
dwyborn
------------------------------------------------------------------------
dwyborn's Profile:
http://www.excelforum.com/member.php...o&userid=29695
View this thread: http://www.excelforum.com/showthread...hreadid=494102

How do I edit a selected range then copy the range into an new sheet???
 

Thanks this works great :o

--
dwybor
-----------------------------------------------------------------------
dwyborn's Profile: http://www.excelforum.com/member.php...fo&userid=2969
View this thread: http://www.excelforum.com/showthread.php?threadid=49410