VBA Excel - find a value in a column, paste to another sheet

Question

I've got a folder of excel worksheets, and also another worksheet with a column whose entries correspond to the file names of the worksheets in the folder.

The column to the right of the worksheet names has a number, which I want to paste into each corresponding worksheet... but it's not working... here's my code so far :

Sub FraisRank()

    Dim folderPath As String
    Dim filename As String
    Dim filenameshort As String
    Dim wb As Workbook
    Dim fraislist As Workbook
    Dim find As Range
    Dim sel As Range

    folderPath = "C:\Users\richard\Desktop\temp" 

    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

    Set fraislist = Workbooks.Open("C:\Users\richard\desktop\frais list.xlsx")

    filename = Dir(folderPath & "*.*")

    Do While filename <> ""
         Application.ScreenUpdating = False
         Set wb = Workbooks.Open(folderPath & filename)
         filenameshort = Left(filename, Len(filename) - 4)

         Set sel = fraislist.Sheets(1).Range("A1:A164")

             Set find = sel.find(What:=filenameshort, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If find Is Nothing Then
            MsgBox ("Cell " & filenameshort & " not found")

        Else
            find.Offset(, 1).Resize(1, 1).Copy
            ActiveSheet.Range("$H$5").PasteSpecial Paste:=xlPasteValues
        End If

         ActiveWorkbook.Save

         ActiveWorkbook.Close

        filename = Dir
     Loop

     End Sub

For the moment I'm getting a Runtime error '13', type mismatch on the 'Set find = ...' part. And in general I don't really understand how to run the '.find' on the selected cells in the 'fraislist' workbook...

BIRDSVIEW: Change `After:=ActiveCell` to `After:=fraislist.Sheets(1).Range("A1")` — Siddharth Rout, Nov 14 '13 at 09:15
Siddharth thanks that was the problem ! Make an answer out of it and I'll select — Petrov, Nov 14 '13 at 10:21

score 1 · Accepted Answer · edited May 23 '17 at 11:58

The problem with ActiveCell is that it will always refer to the Activesheet and hence statements like Activecell/Select/Activate/ActiveSheet/Activeworkbook should be avoided. Always create relevant objects and work with them

INTERESTING READ

In your case it is not necessary that the ActiveSheet is fraislist.Sheets(1) so ActiveCell might not be referring to the correct sheet and hence, it's better to qualify it completely.

If you change After:=ActiveCell to After:=fraislist.Sheets(1).Range("A1") then your code will refer to the correct sheet and it will work.

VBA Excel - find a value in a column, paste to another sheet

1 Answers1