When you refer to unqualifed ranges (like Range("D11")) in a procedure that is
behind a worksheet, then that unqualified range refers to the worksheet with the
code--not the activesheet (Sheet2) in your code.

I'm confused about your =vlookup() formula. Your commandbutton is on sheet1, so
you don't need to specify sheet1 in the =vlookup() formula.

And you're bringing back the first column in that range. Isn't that gonna be
either the same value that you're matching on--or #n/a?

But this may give you a direction:

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range

Application.ScreenUpdating = False

With Me.Range("d10")
.Formula = "=VLOOKUP($c7,sheet2!$d$8:$bp$145,1,FALSE)"
Set RngToCopy = .Resize(1, 65)
End With

Set DestCell = Worksheets("sheet2").Range("D11")

RngToCopy.Copy
DestCell.PasteSpecial.PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Application.ScreenUpdating = True

End Sub



Phin wrote:

Hi all,

Thanks in advance for your advice.

Ihave two worksheets and I'm attempting to create a sub for a command button
(on worksheet 1) that finds the relevent data from worksheet 2, selects the
range and then Paste/ Transpose's it back onto the original worksheet (1).

The Vlookup function seems to work, but all that gets pasted is the first
cell and the transpose doesnt occur. Could anyone tell me where I'm going
wrong..

This is the code so far:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Range("d10").Select
ActiveCell.Formula = "=VLOOKUP($c7,'Sheet1'!$d$8:$bp$145,1,FALSE)"
ActiveCell.Resize(1, 65).Select

Selection.Copy
Sheets("Sheet2").Activate
Range("d11").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

Range("a1").Select
Application.ScreenUpdating = True

End Sub

--

Dave Peterson