CODEWITHSUNDEEP
excelvba
M_S_SAJJAN
M_S_SAJJAN

Reputation: 167

How to select the range for pasting using vba

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.

Upvotes: 0

Views: 970

Answers (1)

Jook
Jook

Reputation: 4682

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

Upvotes: 1

Related Questions