Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i edit only a selected range of data in an excel chart? | Charts and Charting in Excel | |||
How do I prevent copy and pasting into a selected range of a works | Excel Discussion (Misc queries) | |||
define range name, use on VBA selected sheet | Excel Programming | |||
Nameing a range while still selected from copy | Excel Programming | |||
How to Copy & Paste selected Range in Excel (MFC) | Excel Programming |