Code to paste row from another sheet directly below row in which a column parameter is met

[cyberbiker]

I have a spreadsheet I'm building where sheet 1 (PM List) is populated with equipment data and sheet 2 (Checklist Data) is populated with equipment checklists. What I am trying to find is the base code where the following would take place:
If a cell in column "F" of sheet 1 contains the word "stim", row 1 of sheet 2 is copied and pasted into a new row inserted directly below the row in which the word "stim" occurred on sheet 1. While continuing to check only column "F" on sheet 1, if the word "scope" is found, row 4 of sheet 2 is copied and pasted into a new row inserted directly below the row in which "scope" had occurred on sheet 1.

Any ideas? I am admittedly a newbie, but can't find anything that even gets me started on this - and have created other code in the past with what I could find and modify from the web, This has me stymied.

Thanks for any help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub cyberbiker()
   Dim i As Long
   
   Application.ScreenUpdating = False
   With Sheets("Sheet1")
      For i = .Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
         If .Cells(i, 6).Value = "stim" Then
            Sheets("Sheet2").Rows(1).Copy
            .Rows(i + 1).Insert
         ElseIf .Cells(i, 6).Value = "scope" Then
            Sheets("Sheet2").Rows(4).Copy
            .Rows(i + 1).Insert
         End If
      Next i
   End With
   Application.CutCopyMode = False
End Sub

 

[cyberbiker]

FANTASTIC!!

Thanks, Fluff!

Now I have my start on this; There are actually going to be on the order of 30 "stacked" If statements, but this gets me where I need to be as far as getting it all started and running.

Thanks again - there are going to be several dozen Biomedical Equipment Techs who'll be quite happy once this is finished.

 

[Fluff]

In that case you would probably be better off with select case, rather than else ifs.

Sub cyberbiker()
   Dim i As Long
   
   Application.ScreenUpdating = False
   With Sheets("Sheet1")
      For i = .Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
         Select Case LCase(.Cells(i, 6).Value)
            Case "stim"
               Sheets("Sheet2").Rows(1).Copy
               .Rows(i + 1).Insert
            Case "scope"
               Sheets("Sheet2").Rows(4).Copy
               .Rows(i + 1).Insert
         End Select
      Next i
   End With
   Application.CutCopyMode = False
End Sub

 

[cyberbiker]

Even Better!

I've been tweaking it - some of the checklists are multiple rows and I have that figured out. They will all have at least one data validation dropdown, and I have that working so that they insert into the sheet without losing their contexts.
Now I get to start my build in earnest.
Thanks again, Fluff, you have been a great help!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[cyberbiker]

Well, I have had a breakdown that I can't figure out. The attached is the code I have at the moment. The original "stim" actually imports into sheet 1 as "Stimulator, Muscle". No matter what I do, I can't get it to recognize the word "Stimulator". I can get it to work with the word "Muscle", but not if the "Stimulator" is in the cell, also.

Thoughts?

Sub AddChecklists()
   Dim i As Long
   
   Application.ScreenUpdating = False
   With Sheets("Pm List")
      For i = .Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
         Select Case LCase(.Cells(i, 6).Value)
            Case "Stimulator, Muscle"
               Sheets("Checklist Data").Rows(2).Copy
               .Rows(i + 1).Insert
            Case "scope"
               Sheets("Checklist Data").Rows(5).Copy
               .Rows(i + 1).Insert
         End Select
      Next i
   End With
   Application.CutCopyMode = False
End Sub

 

[Fluff]

Each case needs to be in lower case as this line

LCase(.Cells(i, 6).Value)

converts the cell value to lower case in order to do the comparison.
That way it will find scope, Scope, SCOPE etc.

 

[cyberbiker]

SMH

I've been going crazy for an hour trying to figure out how I broke it.

Thanks yet again.

 

[Fluff]

My pleasure.