automatically insert a row
 

Hello all
If anyone can help me with this query i'd be very grateful. I'll try and
explain it as simply as possible.

I have the following data. Columns A, B,C and E are manually populated,
columns D, and F are formulaic entry cells.

Sample data (normally about 1500 rows of data)

A B C D E
F
Cape Trust Discharge TCOV 50 55
a
Cape Tavor Load RELT 60 54 d
Aquabreeze Discharge OVOV 30 53 f
Bet Performer Discharge TCTO 27 45 r

If column C equals either RELT or OVOV i want to automatically add a new row
beneath each which contains the same manual entry data as the row being
copied (columns A, C and E), but also maintain the formula calc as in columns
D and F.

For column B which is a manual entry cell i'd like it to return a blank cell
rather than return the data from the copied cell.

i'm guessing this needs to be done as a macro, however, given my weakness
with macros if any one can help i'd be grateful if you can be as detailed as
possible. Many thanks in advance

Regards

Rich

automatically insert a row
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "C").Value = "RELT" Or .Cells(i, "C").Value =
"OVOV" Then

.Rows(i + 1).Insert
.Rows(i).Copy .Rows(i + 1)
.Cells(i + 1, "B").Value = ""
End If
Next i
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich Hayes" wrote in message
...
Hello all
If anyone can help me with this query i'd be very grateful. I'll try and
explain it as simply as possible.

I have the following data. Columns A, B,C and E are manually populated,
columns D, and F are formulaic entry cells.

Sample data (normally about 1500 rows of data)

A B C D E
F
Cape Trust Discharge TCOV 50 55
a
Cape Tavor Load RELT 60 54 d
Aquabreeze Discharge OVOV 30 53 f
Bet Performer Discharge TCTO 27 45 r

If column C equals either RELT or OVOV i want to automatically add a new
row
beneath each which contains the same manual entry data as the row being
copied (columns A, C and E), but also maintain the formula calc as in
columns
D and F.

For column B which is a manual entry cell i'd like it to return a blank
cell
rather than return the data from the copied cell.

i'm guessing this needs to be done as a macro, however, given my weakness
with macros if any one can help i'd be grateful if you can be as detailed
as
possible. Many thanks in advance

Regards

Rich

automatically insert a row
 

Hi Bob

Thanks for the quick response.

Can you explain please what this is doing, i've just copied it into VBA and
it is saying that it doesn't recognise Public Sub process dAta()

Also in the statement If.cells(i, "c"). value etc . Does the "c" denote
column or something else?

Thanks

Rich

"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "C").Value = "RELT" Or .Cells(i, "C").Value =
"OVOV" Then

.Rows(i + 1).Insert
.Rows(i).Copy .Rows(i + 1)
.Cells(i + 1, "B").Value = ""
End If
Next i
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich Hayes" wrote in message
...
Hello all
If anyone can help me with this query i'd be very grateful. I'll try and
explain it as simply as possible.

I have the following data. Columns A, B,C and E are manually populated,
columns D, and F are formulaic entry cells.

Sample data (normally about 1500 rows of data)

A B C D E
F
Cape Trust Discharge TCOV 50 55
a
Cape Tavor Load RELT 60 54 d
Aquabreeze Discharge OVOV 30 53 f
Bet Performer Discharge TCTO 27 45 r

If column C equals either RELT or OVOV i want to automatically add a new
row
beneath each which contains the same manual entry data as the row being
copied (columns A, C and E), but also maintain the formula calc as in
columns
D and F.

For column B which is a manual entry cell i'd like it to return a blank
cell
rather than return the data from the copied cell.

i'm guessing this needs to be done as a macro, however, given my weakness
with macros if any one can help i'd be grateful if you can be as detailed
as
possible. Many thanks in advance

Regards

Rich

automatically insert a row
 

There should be no space before Data.

And yes, the "C" refers to the column it is processing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich Hayes" wrote in message
...
Hi Bob

Thanks for the quick response.

Can you explain please what this is doing, i've just copied it into VBA
and
it is saying that it doesn't recognise Public Sub process dAta()

Also in the statement If.cells(i, "c"). value etc . Does the "c" denote
column or something else?

Thanks

Rich

"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "C").Value = "RELT" Or .Cells(i, "C").Value =
"OVOV" Then

.Rows(i + 1).Insert
.Rows(i).Copy .Rows(i + 1)
.Cells(i + 1, "B").Value = ""
End If
Next i
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Rich Hayes" wrote in message
...
Hello all
If anyone can help me with this query i'd be very grateful. I'll try
and
explain it as simply as possible.

I have the following data. Columns A, B,C and E are manually populated,
columns D, and F are formulaic entry cells.

Sample data (normally about 1500 rows of data)

A B C D E
F
Cape Trust Discharge TCOV 50 55
a
Cape Tavor Load RELT 60 54 d
Aquabreeze Discharge OVOV 30 53 f
Bet Performer Discharge TCTO 27 45 r

If column C equals either RELT or OVOV i want to automatically add a
new
row
beneath each which contains the same manual entry data as the row being
copied (columns A, C and E), but also maintain the formula calc as in
columns
D and F.

For column B which is a manual entry cell i'd like it to return a blank
cell
rather than return the data from the copied cell.

i'm guessing this needs to be done as a macro, however, given my
weakness
with macros if any one can help i'd be grateful if you can be as
detailed
as
possible. Many thanks in advance

Regards

Rich

automatically insert a row
 

Thankyou very much, it works just as i hoped

"Bob Phillips" wrote:

There should be no space before Data.

And yes, the "C" refers to the column it is processing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich Hayes" wrote in message
...
Hi Bob

Thanks for the quick response.

Can you explain please what this is doing, i've just copied it into VBA
and
it is saying that it doesn't recognise Public Sub process dAta()

Also in the statement If.cells(i, "c"). value etc . Does the "c" denote
column or something else?

Thanks

Rich

"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "C").Value = "RELT" Or .Cells(i, "C").Value =
"OVOV" Then

.Rows(i + 1).Insert
.Rows(i).Copy .Rows(i + 1)
.Cells(i + 1, "B").Value = ""
End If
Next i
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Rich Hayes" wrote in message
...
Hello all
If anyone can help me with this query i'd be very grateful. I'll try
and
explain it as simply as possible.

I have the following data. Columns A, B,C and E are manually populated,
columns D, and F are formulaic entry cells.

Sample data (normally about 1500 rows of data)

A B C D E
F
Cape Trust Discharge TCOV 50 55
a
Cape Tavor Load RELT 60 54 d
Aquabreeze Discharge OVOV 30 53 f
Bet Performer Discharge TCTO 27 45 r

If column C equals either RELT or OVOV i want to automatically add a
new
row
beneath each which contains the same manual entry data as the row being
copied (columns A, C and E), but also maintain the formula calc as in
columns
D and F.

For column B which is a manual entry cell i'd like it to return a blank
cell
rather than return the data from the copied cell.

i'm guessing this needs to be done as a macro, however, given my
weakness
with macros if any one can help i'd be grateful if you can be as
detailed
as
possible. Many thanks in advance

Regards

Rich