Paste from one sheet to a specific cell in another sheet . . .

cyberbiker

New Member
Joined
Jul 29, 2020
Messages
8
Office Version
  1. 365
Platform
  1. 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 . . .

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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You cannot copy an entire row and paste it any column other than column A. You would have to copy a range if you want to paste it in column O. Also, you could simplify the task by not having to add cases to your code as the need arises. You could put all your cases into an array and then loop through that array. If you need to add more cases, you would simply add it to the array instead without making the code longer. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your 2 sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top