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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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:

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 20, 2017 at 14:48

danieltakeshi

9399 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simi Over a year ago

thank you danieltakeshi for your help for the second time today :D It worked. I will read your tips so I can be better in future. Also yes I am multiplying with value in B1. getting to show $ in millions.

Collectives™ on Stack Overflow

vba code. pastes values in range instead of whole column

1 Answer 1

Tips:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Tips:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related