0

I've been working on this code for a while and any help is appreciated!

I want to be able to put a known string on the open excel sheet "B7" and file location in "B6". Using these inputs I want to open the other file, look for "B7" value (header for data table), and copy all of the data in that column and then return to workbook 1 to paste it.

I think my find function is working but the macro returns whatever column below whatever cell was already selected upon opening workbook 2.

Sub FindB7()

    Dim Look As Range
    Dim Sh As Worksheet

    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("H1") = Range("B6")
    Goal = Range("B7").Value

    Range("B6").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

    For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
            Set Look = .Cells.Find(What:=Goal)
            If Not Look Is Nothing Then
                adrs = Range(Look.Address).Select
                Data = Range(adrs, Selection.End(xlDown))
                ActiveWindow.Close
            Else
                Data = "Not Found"
            End If
        End With
    Next

    'paste data in original WkBk

    Range("H2").Value = Look
    Range("H3:H1000").Value = Data

'now remove all "extra" data

Cells.Replace What:="#N/A", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub
Miles Fett
  • 711
  • 4
  • 17
Brennan
  • 1
  • 2
    Just an (important) note: See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to clean up the code, and also note that any time you use `Range` or `Cells` you should qualify the workbook/worksheet. – BigBen Oct 16 '19 at 14:19

1 Answers1

0

My reputation is to low to comment but I am able to give an answer, I do have an idea. You are looping through all the sheets, but with the first range declarations no sheet is given, so for example changing Columns("H:H") to Sheet("Sheet1").Column("H:H") might prevent referring to column H on the wrong sheet.

jonadv
  • 434
  • 6
  • 16