PasteSpecial

[csand]

I'm currently setting up a macro that will input a vlookup from another workbook into a sheet, and then copy and paste just the value into another sheet in the first workbook.

Sheets("CopyPaste").Select
 
Worksheets("CopyPaste").Cells(9 + (nRow - 9), 17).Select
 
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],[VendorQuoteData.xls] Quotes!C1:C5,5)"
 
Worksheets("CopyPaste").Cells(9 + (nRow - 9), 17).Select
 
Range(ActiveCell).Copy
 
Sheets("Sheet").Select
 
Worksheets("Sheet").Cells((1 + (nRow - 9)), 27).Select
 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Everything works except for:

Range(ActiveCell).Copy

When I input the actual cell like "A2" it works fine, but this code sequence works in a loop with variables that change the row (nRow).

Any suggestions? Thank you.

 

[Weaver]

activecell is already a range, not an address, so

Range(ActiveCell).Copy

should be

ActiveCell.Copy

HTH

 

[csand]

Hi Weaver,

Thanks for the tip.

It turns out the problem is actually with where I select the cell to paste the value to.

ActiveCell.Select
 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

When I use the above code it works, but displays the values in one column too early.

End code that works is:

Sheets("CopyPaste").Select
Worksheets("CopyPaste").Cells(9 + (nRow - 9), 17).Select
 
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],[VendorQuoteData.xls]Quotes!C1:C5,5)"
 
Worksheets("CopyPaste").Cells(9 + (nRow - 9), 17).Select
ActiveCell.Copy
Sheets("Sheet").Select
ActiveCell.Offset(0, 1).Select
 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Weaver]

I know it's not what you asked, but you don't need all the 'select' stuff, and often getting rid of it can make the code easier to follow, for instance:

Worksheets("CopyPaste").Cells(9 + (nRow - 9), 17).FormulaR1C1 = "=VLOOKUP(RC[-4],[VendorQuoteData.xls]Quotes!C1:C5,5)"
 
Worksheets("CopyPaste").Cells(9 + (nRow - 9), 17).Copy
Sheets("Sheet").Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Should do the same job.

 

[csand]

Thanks for the help. I'm just learning VBA for a project.