Copy and pste value

[sujittalukde]

I need a macro which will copy the row containg the word "Carried over" and paste it to next row as "Brought Forward" However if below the Carried over, the word "Grand Total" exist, then no such copy paste is required.
A sample shot is given below for better understanding.

In case some more clarification required, do let me know.

CarriedOverBroughtFwd.xls
	A	B	C	D
1	Names	Amount
2	a	3
3	b	4
4	c	5
5	Carried over	12	This is the page sub total
6	Brought Forward	12	This is the copy of above row containing "carried over"
7	e	9
8	f	8
9	g	7
10	Carried over	24	This is the page sub total
11	Brought Forward	24	This is the copy of above row containing "carried over"
12	h	7
13	i	4
14	j	3
15	Carried over	14	This is the page sub total
16	Grand Total	50	Since below the Carried over, grand total so no
17			paste value is required from above row
Sheet1

 

[sujittalukde]

Some help please!

 

[sosasola]

For Each cell In Range("A1:A2000") 
'in above line, you may change the range of area that contains or not "carried over"
    
    If cell.Value = "Carried over" Then
        
        If Range("A" & cell.Row + 1).Value = "Grand Total" Then
            'do nothing
            Else
            Range("A" & cell.Row + 1).Value = "Brought Forward"
            Range("B" & cell.Row).Copy
            Range("B" & cell.Row + 1).PasteSpecial
        End If
    End If

Next cell

 

[sujittalukde]

Thanks sosasola, for the reply. THe code need some modification-
1. It is correctly copying and pasting but while pasting the "Brought forward" row, it is overwriting the existing row for eg., for 1st carried over", it is pasting over record "e". THis should actually insert the copied row.

2. The brought forward copied row shall not make any calculation it shall just copy the the value of carried over row something like paste special value.

 

[DiscoPistol]

Private Sub CommandButton1_Click()
For i = 1 To 16
If Range("A" & i).Value = "Carried over" Then
If Range("A" & i).Offset(1, 0).Value <> "Grand Total" Then
Range("A" & i).Cells.EntireRow.Copy
i = i + 1
Range("A" & i).Cells.EntireRow.Insert
Range("A" & i).Value = "Bought Forward"
End If
End If

Next i

End Sub

 

[sujittalukde]

THanks DiscoPistol, you have modified the serial 1 of my last post, but not adressed the second one . The code is inserting new row with "brought forward" but the value is coming through formula.

In the snapshot at my first post, the value at cell B6 shall be equal to B5 either by way of formula (=b5) or by way of paste special value from cell B5.

 

[DiscoPistol]

Try this

Private Sub CommandButton1_Click()
For i = 1 To 16
If Range("A" & i).Value = "Carried over" Then
If Range("A" & i).Offset(1, 0).Value <> "Grand Total" Then
Range("A" & i).Cells.EntireRow.Copy
i = i + 1
Range("A" & i).Insert
Range("A" & i).Value = "Bought Forward"
Range("A" & i).Cells.EntireRow.PasteSpecial xlPasteValues

End If
End If

Next i

End Sub

 

[sujittalukde]

THis is working a bit but when it is pasting the row, instead of writing "brought forward", it is writing "carried over"

 

[DiscoPistol]

Doh'

Swap these 2 lines over

Range("A" & i).Value = "Bought Forward" 
Range("A" & i).Cells.EntireRow.PasteSpecial xlPasteValues

DP

 

[sosasola]

or this:

For Each cell In Range("A1:A2000") 'here you may change the range of area that contains or not "carried over"
    cell.Select
    If cell.Value = "Carried over" Then
        
        If Range("A" & cell.Row + 1).Value = "Grand Total" Then
            'do nothing
            Else
                If Range("A" & cell.Row + 1).Value <> "Brought Forward" Then
                    ActiveCell.EntireRow.Copy
                    ActiveCell.EntireRow.Insert shift:=xlDown
                    Range("A" & cell.Row).Value = "Brought Forward"
                    Else
                    Range("A" & cell.Row + 1).Select
                    Range("B" & cell.Row + 1).Value = Range("B" & cell.Row).Value
                End If
        End If
    End If

Next cell