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

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

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