0

I have a worksheet named Data where I listed products in rows. The information for each product are in columns A:W. The column A is used for companies ID. Depends on the ID selected in a dropdown list, I want the cells P:W to be pasted in a worksheet named Quote starting from row 11 in columns A:H. It must be noted the column W in the worksheet Data is used for pictures.

I found some codes over the Web and tried to adapt it to my case with no success. First, I cannot copy paste the pictures. I assume I should use the Range.Copy method instead of PasteSpecial method, but I cannot make it works in my code. Second, I could not find a proper way to copy and paste only the cells from columns P:W. 

Sub Quote()

Dim Data As Worksheet
Dim Quote As Worksheet
Dim CompanyID As String
Dim Finalrow As Integer
Dim i As Integer

Set Data = Sheet3
Set Quote = Sheet2
CompanyID = Sheet6.Range("E5").Value

Data.Select
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Finalrow
    If Cells(i, 1) = CompanyID Then
    Range(Cells(i, 1), Cells(i, 23)).Copy
    Quote.Select
    Rows(11).Select
    Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Data.Select
    End If

Next i

Quote.Select
Range("A11:O200").Delete
Range("A1").Select

End Sub
D.Fox
  • 43
  • 1
  • 7
  • Right now you are copying A to W because of this line `Range(Cells(i, 1), Cells(i, 23)).Copy`. The `Cells(i, 1)` points to column 1 or A. If you use 16 instead of 1, you start copying from column P. – Alex de Jong Mar 01 '19 at 08:09
  • How I could miss that… Thanks a lot. It fixes half of the problem. What remains now is how to get the pictures copy-pasted. – D.Fox Mar 01 '19 at 09:32
  • I don't think you can copy/paste shapes by copy/pasting a range. I think you need to do that separately with something like explained here: https://stackoverflow.com/a/48950861/3927703 – Alex de Jong Mar 01 '19 at 09:41
  • I tested Range.Copy and PasteSpecial methods. With the PasteSpecial method, the one used here. It does not seem possible to copy paste the data and the picture by using PasteSpecial. But it worked fine with "Range.Copy _ Detination:=". The issue is I was not able to use it instead of PasteSpecial in the above code. – D.Fox Mar 01 '19 at 11:16

1 Answers1

0

Try 

Data.Range(Cells(i, 16), Cells(i, 23)).Copy Quote.Range("A200").End(xlUp).Offset(1, 0).resize(1,8)

You can delete all the .Select rows in your code. In Excel automation .Select is almost never necessary. Also you have already referenced the worksheets. If you use those references in determining the range, you won't have to go to the sheets.

Alex de Jong
  • 1,257
  • 1
  • 11
  • 23
  • I'm impressed, I now have the pictures pasted too. I'm also puzzled as I do not know yet what in your piece of code could bring such a change and I will look at the matter tomorrow. And thanks for the advice about .Select. – D.Fox Mar 01 '19 at 13:01