.Copy Destination: PLUS .PasteSpecial Paste:=xlPasteValues 1

[PWD]

Hi there. I've received help from here to get me so far but.......... I have a summary sheet which I run down and find data on some other sheet and copy back to the cell next to the ActiveCell. I then have the complication that the data is a formula so I found, and used

.PasteSpecial Paste:=xlPasteValues

but that seems to make the focus change from what was the ActiveCell to the cell below the pasted one. Here is the version that works just great if what I am copying is data

Sub TestDesFindCell2B()
    Dim Found As Range
    Dim ws As Worksheet
    Dim count
    
    Range("A1").Select

Do Until ActiveCell = ""
    count = 0
    
    For Each ws In ThisWorkbook.Sheets
         With ws.Cells
         
            Set Found = ws.Cells.find( _
                What:=ActiveCell.Value, _
                After:=ws.[A1], _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
            If Not Found Is Nothing Then
                If count > 0 Then
                    With Found.Offset(0, 1)
                        .Copy Destination:=ActiveCell.Offset(0, 1)
                    End With
GoTo line10 [COLOR=green]'I've already found a match so stop searching[/color green]

                End If
            End If
            count = count + 1
        End With
    Next
line10:
Selection.Offset(1, 0).Select
Loop

End Sub

.... and here's the bit I substituted to paste the value

                    With Found.Offset(0, 1)
                        .Copy
                        ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
                    End With

I know I can 'Set' the ActiveCell and return to it but I just wondered if there were an elegant way to combine the 'smoothness' of

.Copy Destination:

with the practicality of

.PasteSpecial Paste:=xlPasteValues

If not it's not really a problem. Just thought I'd ask.

Many thanks.

Des.

 

[SkipVought]

Hi,

I would advise against using the Select and Activate methods to reference sheets and ranges within your code.

Reference each range with an explicit sheet object.

                    With Found.Offset(0, 1)
                        .Copy Sheets("SomeSheetName").Cells(lRow, iCol).Offset(0, 1)
                    End With

Skip,

_[red][/red]

 

[AnotherHiggins]

instead of using copy 'n' paste, why not just use something like the following?

Sheets(2).Range("a1") = Sheets(1).Range("c1").Value

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PWD]

Hi Skip, you helped me with this one last week and some fabulous code. I'm doing this for one of our users (I work on the Helpdesk) and there's every possibility that he could change the name of his first worksheet. I'll stick a button on so all he has to do is press it!! It's just the fact that I need to paste the value and once I do that the one below the pasted cell becomes the active cell which isn't what I want at all.

Hi John, The 'problem' relates to part numbers on a summary sheet that will correspond to a part number contained on any one of any number of sheets that represent different grades and categories of parts. The data is scattered anywhere within the workbook so the 'find' is essential.

Des.

 

[AnotherHiggins]

Paste Special does nothing to change the active cell. This line of code at the end of your macro:

line10:
Selection.Offset(1, 0).Select

will select the cell below the currently active one.

Step through the code by pressing F8 in the VBE to see what each line of code does.

Using the method I selected is not really any better than copying I don't suppose, but it will work. Thy the following change to your code:

            If Not Found Is Nothing Then
                If count > 0 Then
                    [b]activecell.offset(0,1) = Found.Value[/b]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PWD]

Hi John, yes I just love the F8 facility.

Rock 'n' bl00dy Roll!!

ActiveCell.Offset(0, 1) = Found.Offset(0, 1).Value

Now that's what I call elegant.

Many, many thanks.

I guess I'm still a bit Neanderthal and step through each row of the sheet hence the,

line10:
Selection.Offset(1, 0).Select
Loop

I'm sure I should try a For...Next for NumRows or something like that but I've run out of time to refine it. (It's now 17:40 in the UK)

There's always next time!!

Des.