Copy and pste value

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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
ABCD
1NamesAmount
2a3
3b4
4c5
5Carried over12This is the page sub total
6Brought Forward12This is the copy of above row containing "carried over"
7e9
8f8
9g7
10Carried over24This is the page sub total
11Brought Forward24This is the copy of above row containing "carried over"
12h7
13i4
14j3
15Carried over14This is the page sub total
16Grand Total50Since below the Carried over, grand total so no
17paste value is required from above row
Sheet1
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sosasola

New Member
Joined
Jan 26, 2007
Messages
29
Code:
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

Well-known Member
Joined
Jun 2, 2007
Messages
520
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

Active Member
Joined
Jun 6, 2006
Messages
261
Code:
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

Well-known Member
Joined
Jun 2, 2007
Messages
520
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

Active Member
Joined
Jun 6, 2006
Messages
261
Try this

Code:
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

Well-known Member
Joined
Jun 2, 2007
Messages
520
THis is working a bit but when it is pasting the row, instead of writing "brought forward", it is writing "carried over"
 

DiscoPistol

Active Member
Joined
Jun 6, 2006
Messages
261
Doh' :eek:

Swap these 2 lines over

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

DP
 

sosasola

New Member
Joined
Jan 26, 2007
Messages
29
or this:


Code:
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
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top