Solved Insert row, copy one cell down, paste new data

February 23, 2015 at 02:57:40
Specs: Windows 8
Hi there. I have a spreadsheet that I'm using to import hundreds of products into Shopify. I need to add a picture of the packaging to the products as an additional picture. Basically, I need to insert a new row, copy down one of the cells from above (column named Handle), and insert the link to the picture into a column called Image Src.

Can this be done automatically? I tried a Macro but it just wants to repeat it on the same row every time, rather than repeating it down the spreadsheet?

Thanks in advance!

Ryan


See More: Insert row, copy one cell down, paste new data

Report •


✔ Best Answer
March 5, 2015 at 12:44:42
I think this does what you want, assuming that the link you want to Paste into Column X is already on your clipboard. Pasting the "link" as you said in your latest response vs. adding a "picture" as you said in your OP is still a little confusing to me.

In any case, try this code and see if it does what you want.

You said you had "hundreds of products". I suggest you test this code on a much shorter version of your spreadsheet, maybe like 5 products to see if it does what you want. As written, it will determine how many "products" are in Column A, so it should work the same for 5 products or 5000 products.

Sub InsertRow_PasteLink()
'Determine last Row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Column A in Reverse Direction
    For nxtRw = lastRw To 2 Step -1
'Insert row, Copy value from Row above
       Rows(nxtRw + 1).Insert _
            Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       Range("A" & nxtRw).AutoFill _
            Destination:=Range("A" & nxtRw & ":A" & nxtRw + 1), _
                         Type:=xlFillDefault
'Paste link from Clipboard
      Range("X" & nxtRw + 1).PasteSpecial xlPasteAll
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 23, 2015 at 03:19:29
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum, then post the macro you have tried. It may just need some modification.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 5, 2015 at 04:40:24
Hi DerbyDad03! Apologies, I missed the email saying I'd had a response to this. Thanks for getting back to me.

Here's the macro code. I just recorded the macro through Excel; I repeated the process 3 times in the macro (insert new row, copy down the text from the cell above in column A, and pasted the link into column X).

You'll save me DAYS if you can work out how to achieve what I need! Thanks in advance!


Sub Macro1()
'
' Macro1 Macro
'

'
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A3"), Type:=xlFillDefault
    Range("A2:A3").Select
    Range("X3").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A4").Select
    Selection.AutoFill Destination:=Range("A4:A5"), Type:=xlFillDefault
    Range("A4:A5").Select
    Range("X5").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A6").Select
    Selection.AutoFill Destination:=Range("A6:A7"), Type:=xlFillDefault
    Range("A6:A7").Select
    Range("X7").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
End Sub


Report •

#3
March 5, 2015 at 05:56:40
We should be able to automate the process, but since I can't see your spreadsheet from where I'm sitting, I'm a little confused.

You said you need to "insert the link to the picture".

As far as I can tell you are trying to PasteSpecial that link in Column X, but I don't see any code that actually copies a link. When I set up a test worksheet, the code just kept pasting the current contents of the clipboard (the last thing I had copied on my system) into Column X. i.e. the code pasted the same thing every time since nothing new was copied. I don't think you are trying to PasteSpecial the same link into X3, X4, X7, etc., so how are you getting the link to each picture onto your clipboard so it can be pasted?

Perhaps a short example of the data in your spreadsheet might help. You can use the same method as you used to post the code (the pre tags) to post your data in a Column/Row format so that it remains aligned correctly in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
March 5, 2015 at 06:09:37
Thanks for getting back to me. Basically each row designates a different product that I'm importing into Shopify, but the boxes we use are the same for each product (well, each line of products), so I need the same link to the picture of the packaging copied in (let's call it URL LINK PACKAGING.JPG)

The following is some data; there are loads of other columns but they don't matter and their data can't be copied down into the new row.

Handle	                                                                                         Image Src
AEPCB001 Gold Plated Knot Intricate Woven Ribbon Design Plain Metal Simply Metal Cufflinks	URL LINK AEPCB001.JPG
AEPCB002 Metal Knot Intricate Woven Ribbon Design Plain Metal Simply Metal Cufflinks	        URL LINK AEPCB002.JPG

The macro needs to add a new row under product 1, copy the Handle from the cell above, and paste URL LINK PACKAGING.JPG into the Image Src column. The same process needs to happen for product 2, 3, 4, etc...

Hope that makes sense?

Thanks!

Edit: I had just copied the link from my browser and pasted that into the cells in the workbook, which I guess is why it just copied what was in your clipboard...

message edited by ElizabethParker


Report •

#5
March 5, 2015 at 12:44:42
✔ Best Answer
I think this does what you want, assuming that the link you want to Paste into Column X is already on your clipboard. Pasting the "link" as you said in your latest response vs. adding a "picture" as you said in your OP is still a little confusing to me.

In any case, try this code and see if it does what you want.

You said you had "hundreds of products". I suggest you test this code on a much shorter version of your spreadsheet, maybe like 5 products to see if it does what you want. As written, it will determine how many "products" are in Column A, so it should work the same for 5 products or 5000 products.

Sub InsertRow_PasteLink()
'Determine last Row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Column A in Reverse Direction
    For nxtRw = lastRw To 2 Step -1
'Insert row, Copy value from Row above
       Rows(nxtRw + 1).Insert _
            Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       Range("A" & nxtRw).AutoFill _
            Destination:=Range("A" & nxtRw & ":A" & nxtRw + 1), _
                         Type:=xlFillDefault
'Paste link from Clipboard
      Range("X" & nxtRw + 1).PasteSpecial xlPasteAll
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 10, 2015 at 06:43:37
You, Sir, are a hero!! It worked perfectly - you've just saved me days of work! A massive thank you! :-)

Report •


Ask Question