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

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

My apologises, I confuse myself at times. Ill explain accurately in more
detail (and with a little more accuracy)€¦

The command button will be on worksheet 2 along with a drop- down data
validation cell (C7) with which the user can select a name. I need the code
to find this name on Sheet 1. Then Resize to include all details of selected
person; and Copy/ Transpose back to sheet 2.

I was unsure how to go about this so probably Vlookup isnt the best option€¦
What would you recommend?


"Dave Peterson" wrote:

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

So you're taking a row with 65 columns of data and transposing it to a single
column with 65 rows?

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

Res = Application.Match(Me.Range("c7").Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

Set DestCell = Me.Range("D11")

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If
End Sub

(If I understand correctly.)

Phin wrote:

My apologises, I confuse myself at times. Ill explain accurately in more
detail (and with a little more accuracy)€¦

The command button will be on worksheet 2 along with a drop- down data
validation cell (C7) with which the user can select a name. I need the code
to find this name on Sheet 1. Then Resize to include all details of selected
person; and Copy/ Transpose back to sheet 2.

I was unsure how to go about this so probably Vlookup isnt the best option€¦
What would you recommend?

"Dave Peterson" wrote:

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


--

Dave Peterson

Absolute Genius!

Thank you, that does exactly what it should.

Out of interest, is there a way to expand this so that if user selects
another name it Paste/Transpose's in the next available column?

If not its no problem, that bit of brilliance will keep me happy for a long
time.



"Dave Peterson" wrote:

So you're taking a row with 65 columns of data and transposing it to a single
column with 65 rows?

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

Res = Application.Match(Me.Range("c7").Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

Set DestCell = Me.Range("D11")

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If
End Sub

(If I understand correctly.)

Phin wrote:

My apologises, I confuse myself at times. I€„¢ll explain accurately in more
detail (and with a little more accuracy)€¦

The command button will be on worksheet 2 along with a drop- down data
validation cell (C7) with which the user can select a name. I need the code
to find this name on Sheet 1. Then Resize to include all details of selected
person; and Copy/ Transpose back to sheet 2.

I was unsure how to go about this so probably Vlookup isn€„¢t the best option€¦
What would you recommend?

"Dave Peterson" wrote:

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


--

Dave Peterson

You're putting the data in row 11. This looks at that row and finds the next
available column:

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

Res = Application.Match(Me.Range("c7").Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

With Me
Set DestCell _
= Me.Cells(11, .Columns.Count).End(xlToLeft).Offset(0, 1)
If DestCell.Column < 4 Then
Set DestCell = .Range("d11")
End If
End With

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

application.cutcopymode = false
End If
End Sub

To guess at the next question...

If you click the button to try to retrieve data that's already been copied:

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range
Dim myCellWithValue As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

With Me
Set myCellWithValue = .Range("C7")
If Application.CountIf(.Range("D11", .Cells(11, .Columns.Count)), _
myCellWithValue.Value) 0 Then
MsgBox "already there!"
Exit Sub
End If
End With

Res = Application.Match(myCellWithValue.Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

With Me
Set DestCell _
= Me.Cells(11, .Columns.Count).End(xlToLeft).Offset(0, 1)
If DestCell.Column < 4 Then
Set DestCell = .Range("d11")
End If
End With

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

application.cutcopymode = false
End If
End Sub

Phin wrote:

Absolute Genius!

Thank you, that does exactly what it should.

Out of interest, is there a way to expand this so that if user selects
another name it Paste/Transpose's in the next available column?

If not its no problem, that bit of brilliance will keep me happy for a long
time.



"Dave Peterson" wrote:

So you're taking a row with 65 columns of data and transposing it to a single
column with 65 rows?

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

Res = Application.Match(Me.Range("c7").Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

Set DestCell = Me.Range("D11")

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If
End Sub

(If I understand correctly.)

Phin wrote:

My apologises, I confuse myself at times. I€„¢ll explain accurately in more
detail (and with a little more accuracy)€¦

The command button will be on worksheet 2 along with a drop- down data
validation cell (C7) with which the user can select a name. I need the code
to find this name on Sheet 1. Then Resize to include all details of selected
person; and Copy/ Transpose back to sheet 2.

I was unsure how to go about this so probably Vlookup isn€„¢t the best option€¦
What would you recommend?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

A Mind reader too!

Brillian stuff, it all worked a treated.

Thanks Again

"Dave Peterson" wrote:

You're putting the data in row 11. This looks at that row and finds the next
available column:

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

Res = Application.Match(Me.Range("c7").Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

With Me
Set DestCell _
= Me.Cells(11, .Columns.Count).End(xlToLeft).Offset(0, 1)
If DestCell.Column < 4 Then
Set DestCell = .Range("d11")
End If
End With

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

application.cutcopymode = false
End If
End Sub

To guess at the next question...

If you click the button to try to retrieve data that's already been copied:

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range
Dim myCellWithValue As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

With Me
Set myCellWithValue = .Range("C7")
If Application.CountIf(.Range("D11", .Cells(11, .Columns.Count)), _
myCellWithValue.Value) 0 Then
MsgBox "already there!"
Exit Sub
End If
End With

Res = Application.Match(myCellWithValue.Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

With Me
Set DestCell _
= Me.Cells(11, .Columns.Count).End(xlToLeft).Offset(0, 1)
If DestCell.Column < 4 Then
Set DestCell = .Range("d11")
End If
End With

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

application.cutcopymode = false
End If
End Sub

Phin wrote:

Absolute Genius!

Thank you, that does exactly what it should.

Out of interest, is there a way to expand this so that if user selects
another name it Paste/Transpose's in the next available column?

If not its no problem, that bit of brilliance will keep me happy for a long
time.



"Dave Peterson" wrote:

So you're taking a row with 65 columns of data and transposing it to a single
column with 65 rows?

Option Explicit
Private Sub CommandButton1_Click()

Dim RngToCopy As Range
Dim DestCell As Range
Dim Res As Variant
Dim RngToMatch As Range

Application.ScreenUpdating = False

With Worksheets("sheet1")
Set RngToMatch = .Range("d8:d145")
End With

Res = Application.Match(Me.Range("c7").Value, RngToMatch, 0)

If IsError(Res) Then
MsgBox "Not found in sheet1 d8:d145"
Else
Set RngToCopy = RngToMatch.Cells(Res, 1).Resize(1, 65)

Set DestCell = Me.Range("D11")

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If
End Sub

(If I understand correctly.)

Phin wrote:

My apologises, I confuse myself at times. I€„¢ll explain accurately in more
detail (and with a little more accuracy)€¦

The command button will be on worksheet 2 along with a drop- down data
validation cell (C7) with which the user can select a name. I need the code
to find this name on Sheet 1. Then Resize to include all details of selected
person; and Copy/ Transpose back to sheet 2.

I was unsure how to go about this so probably Vlookup isn€„¢t the best option€¦
What would you recommend?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson