Sleeper: Inserting a Row under a Specified Value

[Jt13]

Hello,

Our company has an inventory list that we select items from for each job. Each item is assigned a "phase" (Travel, Commissioning, Installation, etc.) When it gets assigned a phase, I want to take the second and third column values in that row and have it populate on another sheet under the appropriate phase. So far I can only insert the row on the first unused row. Any help would be appreciated! Code and photos below:



Sub CopyCatalogtoPickSheet()
Dim PartIDField As Range
Dim PartIDCell As Range
Dim Rng As Range
Dim WorkRng As Range
Dim PSSheet As Worksheet
Dim CSheet As Worksheet
Set PSSheet = Worksheets("Material Pick Sheet")
Set CSheet = Worksheets("Catalog")
Set PartIDField = CSheet.Range("A6", CSheet.Range("A6").End(xlDown))
For Each PartIDCell In PartIDField
If PartIDCell.Value = "Module Installation" Then
PartIDCell.Resize(1, 2).Offset(0, 1).Copy Destination:=PSSheet.Range("A1").Offset(PSSheet.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
End If
Next PartIDCell
PSSheet.Columns.AutoFit
End Sub


30987
30988

[Paul_Hossler]

1. Welcome to the forum

2. Please used CODE tags (that's the [#] icon) since it make the macro much easier to read

3. Take a minute to read the FAQ in the link in my signature

4. And finally, most times it's easier to understand an issue and offer suggestions and testing if you cn attach a small example workbook instead of screen shots, since otherwise people are just guessing

[Aussiebear]

Welcome to VBAX Jt13. If you wish to insert a blank row after every other row then the following should work



Sub Insert_Row_After_Every_Other_Row()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Set rng = Selection
CountRow = rng.EntireRow.Count
For i = 1 To CountRow
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Next i
End Sub

[Jt13]

Hi Aussiebear,

Thanks, but that's not quite what I was looking to do. In the first picture, the 3/4" panhead screw is tagged with "Module Installation". I would like to place it under the "Module Installation" row in the second picture, but I can only get it to insert in the next available row. Hope this clarifies things.

[Aussiebear]

Okay, can you attach a sample workbook to enable us to test any coding please?