CODEWITHSUNDEEP
vbaexcel
user4242750
user4242750

Reputation: 197

CopyPaste macro (Pastespecial, Find and replace)

I have been using a simple copy paste example here and now need to start using paste special and a Find and replace.

For example, I have columns A,B,E. I would like to copy B's contents to E.

So what I have used is:

Sub sbCopyRange()

Range("B2:B10").Copy
Range("E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Now I need it to be a paste special as well as to find and replace all the "^"'s and replace them with (CntrlShift+ J) so that it creates line breaks at all the "^"'s. I thought I could do this with record function but I have noticed some issues when adjusting it for my new range in a bigger document.

Could anyone assist with this?

Upvotes: 2

Views: 828

Answers (1)

user4039065
user4039065

Reputation:

To copy to another destination, you've correctly chosen only the top-left cell as the destination but there is an abbreviated copy command for single operations.

With ActiveSheet  '<- set this to something like With Sheets("Sheet1")
    .Range("B2:B10").Copy _
        Destination:=.Range("E2")
End With

You can also perform a direct value transfer which is the equivalent of a Copy, Paste Special, Values but the destination needs to be resized to fit the source. However, since you want to perform a replacement on the destination cells, you really need its dimensions anyway.

Dim rng As Range
With ActiveSheet  '<- set this to something like With Sheets("Sheet1")
    Set rng = .Range("B2:B10")
    With .Range("E2").Resize(rng.Rows.Count, rng.Columns.Count)
        .Value = rng.Value
        .Replace what:=Chr(94), replacement:=Chr(10), lookat:=xlPart
    End With
End With

Upvotes: 1

Related Questions