Macro to duplicate line

**cossie2k** · 06-11-2012, 08:13 AM

Hi all,

Im hoping you can help me out......

Anything more than recording a macro and Im way out of my depth!

I have a spreadsheet (when I try to upload it says its corrupt)

A - B - C
1 Loosen wheel nuts - Operator -
2 Jack up car - Operator -
3 Inspect jack up - Inspector - Random
4 Remove nuts - Operator -
5 Remove wheel - Operator -
6 Inspect removal of wheel - Inspector - Random
7 Replace wheel - Operator -
8 Replace nuts - Operator -
9 Lower car - Operator -
10 Tighten nuts - Operator -
11 Inspect tighten of nuts - Inspector - Mandatory

Where a random inspection occurs (ie row 3) I want to insert a line and copy the row row above, so that it duplicates the inspection. Where a mandotory inspection occurs I dont want anything to happen.

Is this even possible as when I record a macro it remembers the row number each time so when I have a different sequence, it inserts the row at the wrong point.

Thank you for any help
Alex

**jraj1106** · 06-11-2012, 09:57 AM

Hi

could you put in the output that you want with this explanation?

**cossie2k** · 06-11-2012, 10:12 AM

Hi,

Sure.....

At row 3 for example, I would hope to see a new row being inserted above or below the "random" and then the "random" rows copied into the new row as per below. Hope that makes sense! I need to copy the entire row though

A - B - C
1 Loosen wheel nuts - Operator -
2 Jack up car - Operator -
3 Inspect jack up - Inspector - Random
3 Inspect jack up - Inspector - Random
4 Remove nuts - Operator -
5 Remove wheel - Operator -
6 Inspect removal of wheel - Inspector - Random
6 Inspect removal of wheel - Inspector - Random
7 Replace wheel - Operator -
8 Replace nuts - Operator -
9 Lower car - Operator -
10 Tighten nuts - Operator -
11 Inspect tighten of nuts - Inspector - Mandatory

Thanks for your help

**watersev** · 06-11-2012, 10:18 AM

hi cossie2k, please check attachment, press Run button

**arlu1201** · 06-11-2012, 10:51 AM

Try this code


Option Explicit

Sub inspection()
Dim lrow As Long, i As Long

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("C" & i).Value = "Random" Then
            .Rows(i + 1).Insert
            .Range("A" & i & ":C" & i).Copy .Range("A" & i + 1)
            i = i + 1
            lrow = lrow + 1
        End If
    Next i
End With

End Sub

**cossie2k** · 06-11-2012, 11:04 AM

Thanks Watersev / arlu1201,

They work perfectly! Its been driving me mad for over a week!

If its no trouble, please could you explain the component parts as some of the code makes sense, others though I dont understand how they fit in for example "& i"

Thanks
Alex

**cossie2k** · 06-11-2012, 11:12 AM

If I wanted to manually insert a column in order to add an operation number for example, what would I need to change in the macro? I assumed Id change the "C" to a "D" but this doesnt work now!

**WasWodge** · 06-11-2012, 12:07 PM

Can you just clarify are you talking about Arlettes code and you want it to copy extra columns but the search is still for the word random in column C or do you mean that you want it to search for the "operation number"?

**cossie2k** · 06-12-2012, 04:02 AM

Sorry for the delay in getting back.

I have now figured out how to adjust what I need to with one exception.

I suspect Im pushing my luck here but where the duplicate row goes in, is it possible to change the inspector to operator, just for the new line.

In effect Id like a duplicate inspection row with the first inspection against the inspector and the second row against the operator?

Thanks for all your help
Alex

**arlu1201** · 06-12-2012, 04:04 AM

Try this


Option Explicit

Sub inspection()
Dim lrow As Long, i As Long

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("C" & i).Value = "Random" Then
            .Rows(i + 1).Insert
            .Range("A" & i & ":B" & i).Copy .Range("A" & i + 1)
            .range("C" & i+1).value = "Operator"
            i = i + 1
            lrow = lrow + 1
        End If
    Next i
End With

End Sub

**cossie2k** · 06-12-2012, 04:13 AM

wow!

Thanks for the quick reply!

Ill try it out now!

Macro to duplicate line

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Re: Macro to duplicate line

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions