How to select the range for pasting using vba

Question

I wrote some code for selecting the particular row and pasting it in column wise using paste-special property. It is working correctly my code is :

lngRow = Me.TextBox4.Value
strCol = Me.TextBox5.Value
Set rng = Range("A:A").Find(What:=lngRow, LookIn:=xlValues, LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "Value not found in row 1", vbExclamation
Else
 Range(rng, rng.End(xlToRight)).Copy
 Range("A1:E3").Columns(strCol).Offset(, 1).PasteSpecial Transpose:=True
 Range("A1:E3").Rows(1).Copy
 Range("A1:E3").Columns(strCol).PasteSpecial Transpose:=True
endif

the problem here is I am using Range(rng, rng.End(xlToRight)).Copy to copy the values and for pasting I am using Range("A1:E3").Columns(strCol).Offset(, 1).PasteSpecial Transpose:=True.

How can I paste all the values which are copied? Because if the values are in column F then this macro will not paste those values.

please explain you code in the else section more detailed, as what exactly you are trying to accomplish. I am guessing right now, that you want to copy a specific whole row, transposed to a column, into a different column - am I correct? — Jook
Ok, based on that, I gave you an answer. And just as a friendly reminder: it would be nice of you, to accept some of the answers to your question, if they helped you to solve your problem. — Jook

Jook Jook · Accepted Answer · 2012-09-27T09:36:25

This code will paste the whole row, if found, transposed to the given col, starting at row 1.

If it has to start at a different row or col, you should be able to adapt to that.

lngRow = Me.TextBox4.Value
strCol = Me.TextBox5.Value

Set rng = Range("A:A").Find(What:=lngRow, LookIn:=xlValues, LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "Value not found in row 1", vbExclamation
Else
 Range(Rng, Rng.End(xlToRight)).Copy
 Cells(1, strCol).PasteSpecial Transpose:=True
End If

How to select the range for pasting using vba

1 Answers