Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to copy line above when XXX occurs and move on?
Hi,
I am working with a large spreadsheet and need to copy and fill data. I am hoping there is a better way than copy and pasting manually 2000 lines of data. Its pretty basic: in column A there are sub totals. I need to copy the information from the line above the subtotal to the line on the sub-total. if A3 contains the word "total" then copy B2, C2, D2 and E2 to line B3, C3, D3,and E3 respectively. Then move to the next occurrance of a cell in column A that contains the word "total" repeat the copy of the row above until reaching the end of the worksheet. Is there a way to do this? Thanks, Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to copy line above when XXX occurs and move on?
Yes you can do it by code but you can also do it an easier way.
1) Autofilter for the lines that have total in column A 2) Imagine the first cell is A46. Go into cell B46 and type =B45. 3) Now extend this formula to the cells on the same row up to column E, and extend it down to all the other rows you can see. 4) Remove the autofilter 5) Fix the values using copy, edit paste special Values -- Allllen "Todd" wrote: Hi, I am working with a large spreadsheet and need to copy and fill data. I am hoping there is a better way than copy and pasting manually 2000 lines of data. Its pretty basic: in column A there are sub totals. I need to copy the information from the line above the subtotal to the line on the sub-total. if A3 contains the word "total" then copy B2, C2, D2 and E2 to line B3, C3, D3,and E3 respectively. Then move to the next occurrance of a cell in column A that contains the word "total" repeat the copy of the row above until reaching the end of the worksheet. Is there a way to do this? Thanks, Todd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to copy line above when XXX occurs and move on?
Todd,
Try: Sub SameAsAbove() Dim MyRange As Range Dim MyCell As Range Dim Endrow As Integer Endrow = Range("A65536").End(xlUp).Row Set MyRange = Range("A1:A" & Endrow) MyRange.Select 'On Error Resume Next For Each MyCell In MyRange If MyCell.Value = "Total" Then Range("B" & MyCell.Row & ":E" & MyCell.Row).Copy ActiveSheet.Paste Destination:= _ MyCell.Offset(1, 1) End If Application.CutCopyMode = False Next MyCell End Sub Alan Todd wrote: Hi, I am working with a large spreadsheet and need to copy and fill data. I am hoping there is a better way than copy and pasting manually 2000 lines of data. Its pretty basic: in column A there are sub totals. I need to copy the information from the line above the subtotal to the line on the sub-total. if A3 contains the word "total" then copy B2, C2, D2 and E2 to line B3, C3, D3,and E3 respectively. Then move to the next occurrance of a cell in column A that contains the word "total" repeat the copy of the row above until reaching the end of the worksheet. Is there a way to do this? Thanks, Todd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Cell From Subtotal Line To Detail Line | Excel Discussion (Misc queries) | |||
Copy Source Cells to Destination Cells Only when a Change Occurs | Excel Discussion (Misc queries) | |||
Can I Move replace instead of Move copy | Excel Discussion (Misc queries) | |||
Move a value from one range to another - rounding occurs that I do not want....? | Excel Programming | |||
Move equations from line to line automatically | Excel Discussion (Misc queries) |