cyberbiker
New Member
- Joined
- Jul 29, 2020
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
I have a project that I need to change, and haven't been able to figure it out. In the code snippet that I'm posting, it looks for the value in column "F". If it sees a particular value, it copies a row or rows from a different sheet and creates new rows to paste into directly below that value in column "F". It works great, thanks to Fluff's help a couple of months ago. We have hit a snag, in that if the user decides to sort by any column, the rows that were created don't stay with the creating value.
What I am trying to do now is to use the same values in column "F" to trigger the copy from another sheet, but instead of creating new rows, I need it to paste the copied row beginning at column "O" on the same row that the triggering value was found.
For example, if the value "Cell Washer" was seen in cell (F,23) of sheet "PM List", it would copy row 2 from sheet "Checklist Data" and paste it into row 23 beginning at column "O". Since I currently have 16 cases stacked with more coming as time goes on, I don't think that inserting will work, since each possible instance would be adding more and more columns. The data on the "PM List sheet currently ends at column "N", so it is blank from "O" on out.
Thanks for any help - I am lost, once again . . .
What I am trying to do now is to use the same values in column "F" to trigger the copy from another sheet, but instead of creating new rows, I need it to paste the copied row beginning at column "O" on the same row that the triggering value was found.
For example, if the value "Cell Washer" was seen in cell (F,23) of sheet "PM List", it would copy row 2 from sheet "Checklist Data" and paste it into row 23 beginning at column "O". Since I currently have 16 cases stacked with more coming as time goes on, I don't think that inserting will work, since each possible instance would be adding more and more columns. The data on the "PM List sheet currently ends at column "N", so it is blank from "O" on out.
Thanks for any help - I am lost, once again . . .
VBA Code:
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Pm List")
For i = .Range("G" & Rows.Count).End(xlUp).Row To 2 Step -1
Select Case LCase(.Cells(i, 7).Value)
Case "cell washer"
Sheets("Checklist Data").Rows(2).Copy
.Rows(i + 1).Insert
Case "centrifuge"
Sheets("Checklist Data").Rows("7:9").Copy
.Rows(i + 1).Insert