Sub Zeromaim() Dim MyCell As Range, Marker As Range Dim LowerBoundaryForA As Range, LowerBoundaryForB As Range, LowerBoundaryForC As Range With Sheets("Sheet2") Set LowerBoundaryForA = .Range("K12") Set LowerBoundaryForB = .Range("K14") Set LowerBoundaryForC = .Range("K16") End With Sheets("Sheet1").Activate For Each MyCell In Range("G11:G23") Select Case UCase(MyCell.Value) Case Is = "A" Set Marker = LowerBoundaryForA Case Is = "B" Set Marker = LowerBoundaryForB Case Is = "C" Set Marker = LowerBoundaryForC End Select With Marker .EntireRow.Insert .Offset(-1, 0).Value = MyCell.Offset(0, -1).Value .Offset(-1, 1).Value = MyCell.Offset(0, 1).Value .Offset(-1, 2).Value = MyCell.Offset(0, 2).Value End With Next MyCell End Sub
Sub Zeromaim() Dim MyCell As Range, Marker As Range Dim LowerBoundaryForA As Range, LowerBoundaryForB As Range, LowerBoundaryForC As Range With Sheets("Sheet2") Set LowerBoundaryForA = .Range("K12") Set LowerBoundaryForB = .Range("K14") Set LowerBoundaryForC = .Range("K16") End With Sheets("Sheet1").Activate For Each MyCell In Range("G11:G23") Stop Select Case UCase(MyCell.Value) Case Is = "A" Set Marker = LowerBoundaryForA Case Is = "B" Set Marker = LowerBoundaryForB Case Is = "C" Set Marker = LowerBoundaryForC Case Else Set Marker = Nothing End Select If Not Marker Is Nothing Then With Marker .EntireRow.Insert .Offset(-1, 0).Value = MyCell.Offset(0, -1).Value .Offset(-1, 1).Value = MyCell.Offset(0, 1).Value .Offset(-1, 2).Value = MyCell.Offset(0, 2).Value End With End If Next MyCell End Sub
DON'T MISS
Thanks again for the reply, the code was very helpful. I have some follow up question, kindly refer to the post "Need help in Excel Macro" posted by zeromaim.
Kindly insert a code that if the ID is anything other than A, B, and C, the program will just skip to evaluate the next row (no inserting). If column G11:G23 reads as A, B, C, D, E, A, B, C, etc..., the program should not copy-insert the row associated to ID D, E, and other IDs.
Thanks again and hope to hear from you soon.
Anton