CODEWITHSUNDEEP
excelvbacopy-paste
user12691899
user12691899

Reputation: 3

Copy range and transpose paste into row that contains cell

I have a mix of codes I found that seemed to work but no longer does after changing a few things. I am trying to copy values from a range on one sheet ("Sheet1") and paste them transposed onto another ("Sheet2"). The catch is that I only want to paste them into the row that the value in column A equals the value in ("B2") on the same sheet. Also, this value will be repeated throughout column A, but I only need it to paste to the row between rows 11 and 29. Here is what I have so far:

Sub PasteData()

Range("O3:O44").Select
    Selection.copy

Worksheets("Sheet2").Activate

Worksheets("Sheet2").Unprotect ("Password")

Dim nRow As Long
Dim nStart As Long, nEnd As Long

For nRow = 11 To 29
If Range("A" & nRow).Value = Range("b2").Value Then
nStart = nRow
Exit For
End If
Next nRow

For nRow = nStart To 29
If Range("a" & nRow).Value <> Range("b2").Value Then
nEnd = nRow
Exit For
End If
Next nRow
nEnd = nEnd - 1

Range("A" & nStart & ":AP" & nEnd).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Worksheets("Sheet2").Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=False
Worksheets("Sheet3").Activate

Range("B13").Select

End Sub

Upvotes: 0

Views: 111

Answers (1)

sabawcoder
sabawcoder

Reputation: 76

I have noticed on your code that you have not referenced the sheet of Range("O3:O44"). So when you run the code, it will Select and Copy the Range("O3:O44")of the active sheet.

To avoid this confusion, avoid using .Select and .Activate as much as possible especially when dealing with multiple sheets. When referencing Ranges, always include the sheet you are targeting to.

So instead of:

Range("O3:O44").Select
Selection.Copy

Do it like this:

Worksheets("Sheet1").Range("O3:O44").Copy

Now to answer your problem, you need to indicate what sheet Range("O3:O44") is from. Then move this code on the line just before pasting it.

'range to copy with sheet reference
Worksheets("Sheet1").Range("O3:O44").Copy
'range where previous range will be pasted, also with sheet reference
Worksheets("Sheet2").Range("A" & nStart & ":AP" & nEnd).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Upon trying your code, this is the solution to the error you encounter.

Upvotes: 1

Related Questions