PDA

View Full Version : VBA - Find, Copy then Paste Special



meclive
09-01-2008, 05:15 AM
Help! im stuck!
Im trying to get some VBA to search in my worksheet to find a match to the value of cell B5 (the value of B5 changes depending on user choice), then once a match is found I want to highlight from the matched cell to the right 5 columns and down to row 193 (so in total 6 columns would be highlighted down to row 193), then copy and paste special values over those cells.
Any ideas? any help much appreciated! thanks.

My code so far:

Sub test2()
'
' test2 Macro
'
Dim rng As Range
Dim strDate

strDate = Range("B5").Value

Set rng = Range("26:26").Find(What:=strDate, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

If Not rng Is Nothing Then
With rng.Resize(193 - rng.Row + 1, 6)
.Value = .Value
End With
End If
rng.Select
Selection.Copy

'paste special values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Bob Phillips
09-01-2008, 05:19 AM
And what happens with your code, what is the problem?

meclive
09-01-2008, 05:24 AM
at the moment it is just copying the first cell it matches - its not going accross 5 columns and down to row 193.

meclive
09-01-2008, 06:01 AM
my mistake - i was being stupid and tried to paste special values when it had already pasted the data.

this thread can be closed. Thanks for your help

MWE
09-01-2008, 02:09 PM
my mistake - i was being stupid and tried to paste special values when it had already pasted the data.

this thread can be closed. Thanks for your helpPart of the value of this forum is that the process of explaining what you are doing often results in you realizing what is wrong. You are not stupid, you are just overlooking the particular cause. If we did not allow forum participation because of "stupid" errors, there would be no one logged on including all "the experts" :devil2: