CODEWITHSUNDEEP
excelvba
Tamell1
Tamell1

Reputation: 3

Find Value in another workbook and copy/paste a range of cells

I am new to VBA and have cobbled this together from code I found in forums and the code generated by a recorded macro. I think it is close, but there is one issue I cannot figure out after much searching. Your help is appreciated.

I have a Worksheet in wb1 with a Value in cell B3, and I want to search for that value in wb2 (across multiple sheets). When the value is located, I want to select a range of cells in the worksheet where the value was found and then copy that back to wb1.

The code successfully finds the value in wb2. It selects a range of cells and copies it back to wb1. The problem is that it does not select the cells from the desired worksheet (where the value was actually found). It selects the range of cells from whichever sheet wb2 was opened to. For example, if Excel opened wb2 to Sheet1, and my Value is found in Sheet3, it still copies the range from Sheet1.

I need it to find the cells in a Sheet, and then copy from that Sheet. This is what I have so far (I have a couple of message boxes to see if the code was correctly looping through the sheets and finding something):

Sub PasteTemplate()

    Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
    Dim Dest As Worksheet
    Set Dest = ActiveSheet
    Dim FindValue As String
    FindValue = Range("B3").Value
    Dim PasteHere As Range
    Set PasteHere = Range("A5")
        
    'This section opens wb2 if it is not already open
    Dim fStatus As Long
        Err.Clear
        On Error Resume Next
    Set wb2 = Application.Workbooks.Open(filename:="Templates.xlsx", UpdateLinks:=False)
        fStatus = Err
        On Error GoTo 0
        If fStatus Then
            Set wb2 = Application.Workbooks.Open(filename:="Templates.xlsx", UpdateLinks:=False)
        End If
        
    Dim FoundValue As Range
        
        For Each Sheet In wb2.Sheets
            With Sheet.Columns("B")
                Set FoundValue = .Find(What:=FindValue, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=True, SearchFormat:=False)
                    If FoundValue Is Nothing Then
                        msgbox "Value not found in " & Sheet.Name
                        End If
                    If Not FoundValue Is Nothing Then
                        msgbox "Found the value in " & Sheet.Name
                        Range("A1:Z100").Select '//Where the problem is. It selects the range in the sheet where wb2 was opened to
                        Application.CutCopyMode = False
                        Selection.Copy
                    End If
            End With
        Next
        
    wb1.Activate
    PasteHere.Select
    ActiveSheet.Paste

End Sub

Upvotes: 0

Views: 2755

Answers (1)

Christofer Weber
Christofer Weber

Reputation: 1474

Just having Range(). is the same as having ActiveSheet.Range(). So it's always good to specify the sheet. This goes for the variables as well, like PasteHere

Also, the obligatory link to how to avoid using select.

Can't test it, but maybe something like this:

Since you are using a for each loop, we have the current sheet as a variable ready to use when referring to the range.

  ...  
    For Each Sheet In wb2.Sheets
        With Sheet.Columns("B")
            Set FoundValue = .Find(What:=FindValue, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True, SearchFormat:=False)
                If FoundValue Is Nothing Then
                    msgbox "Value not found in " & Sheet.Name
                    End If
                If Not FoundValue Is Nothing Then
                    msgbox "Found the value in " & Sheet.Name
                    Sheet.Range("A1:Z100").Copy PasteHere
                End If
        End With
    Next
End Sub

Upvotes: 1

Related Questions