vba code. pastes values in range instead of whole column

Question

Can someone help me with below issue please :)

Issue: I have below code that finds textA in Budget sheet in column A copies that row... finds sheet with same textA and pastes in d4:d15. however it is pasting through out column D instead of just in that range.

Help with : get vba to copy only B:M row range from budget sheet and paste in column d4:d15 only.

Sheets("Budget").Select
Columns("A:A").Select
On Error Resume Next
Selection.Find(What:="phh budget", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
If Err.Number = 91 Then
MsgBox "ERROR: 'PHH budget' could not be found."
End
End If


intRow = ActiveCell.Row
Rows(intRow & ":" & intRow).Copy
Sheets("PHH").Select
range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

range("B1").Copy
Application.CutCopyMode = False
Selection.Copy
range("D3:D15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False
Columns("D:D").EntireColumn.AutoFit

I appreciate any help.

Answer 1

You should change Rows(intRow & ":" & intRow).Copy to Range("B" & intRow & ":M" & intRow).Copy

Because with your first code, you are Copying the entire Row and then pasting. With the second code, You just copy from column B to M.

And i don't know if it is what you intended, but you are multiplying the values that are pasted.

Tips:

1. You should avoid How to avoid using Select in Excel VBA. So you declare the worksheet and range.
2. Try to copy this way with Range.Copy and Pasting with Transpose.
3. Try using the example of the Range.Find Method
4. If you try to get rid of Select, you should also substitute: .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook