Find particular cells to copy to main workbook

Question

I want to find a particular cell in an opened worksheet and copy the data 3 cells to the right into the main workbook (not the one that has just been opened).

The macro below loops through all the files in a txt doc and opens and closes them. In each file is a cell called ADA and I want to copy the cells 3 to the right, however ADA is not always in the same place so I need to search for it. e.g find the cell ADA and say it is in E6 in that workbook, I need to copy E6 along with H6,I6 and J6 into the original main workbook that I am running the macro from.

Sub GatherData()
Dim objFSO As Object
Dim objWB As Workbook
Dim strFN As String
Dim objTF As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile("U:\Time series project\doclist.txt")
On Error Resume Next
Do While Not objTF.AtEndOfStream
    strFN = objTF.readline()
    Set wb = Workbooks.Open(strFN)
    If wb Is Nothing Then
        Debug.Print strFN
    Else    
        wb.Close False
        Set wb = Nothing
    End If
Loop
On Error GoTo 0
End Sub

I was thinking of adding something like

Dim c As Range
Dim newcell
Dim tmp
Dim wrkbk As WorkBook
Dim sht As WorkSheet

Set c = .Find(findValues(i), LookIn:=xlFormulas)
If Not c Is Nothing Then
    newcell = c.Offset(0, 4).Value
    Do
        tmp.Offset(0, 2).Value = tmp.Value
        tmp.Offset(0, 3).Value = newcell
End If

but can't get this concept to fit into the first code.

The txt files are, Comma delimited CSV? Text Tab Delimited?? Text Space delimited??? What the files got inside?? Strings of the books or the data you want to manipulate??? — Elbert Villarreal, Feb 08 '16 at 14:52

score 0 · Answer 1 · answered Feb 05 '16 at 17:03

0

You can simply use

Dim copyrange as Range
Dim originalcell, startingcell, endingcell as String

    originalcell = sht.Range("ADA").Address
    startingcell = sht.Range("ADA").Offset(0,3).Address
    endingcell = sht.Range("ADA").Offset(0,5).Address

    copyrange = originalcell & "," & startingcell & ":" & endingcell

Then just copy that range and paste where you would like.

answered Feb 05 '16 at 17:03

Dan

425
2
13

Thanks for the idea! the trouble with this is what i need to do is loop through say 200 workbooks taking one line from each and then creating a table in the main workbook. Its to much to paste manually – IIJHFII Feb 05 '16 at 17:08
Please specify further what you have a question about in your post then. The solution provided will "find" a named range in any workbook, simply use ActiveWorkbook or ActiveSheet if you are looping through and activating each one. Please clarify and include any other details relevant to your desired solution and I'll be happy to help. – Dan Feb 05 '16 at 17:15
Hi Dan, thanks for the help so far! the problem I am having is that I am getting no output. I am looking to get the cells that I have found back into the main original workbook that the macro is running from but this isn't happening? – IIJHFII Feb 08 '16 at 09:11
If you are trying to get cells you've found back into the original workbook, specify the workbook before pasting. My answer provided how to locate the range to be copied. For pasting into different workbooks, you'll need to specify the open workbook before using "pastespecial". `Workbooks("DestinationWorkbook").Worksheets("DestinationSheet").Range("DestinationRange").PasteSpecial` [Link explaining different ways to paste in separate workbooks](http://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another) – Dan Feb 09 '16 at 16:13

Find particular cells to copy to main workbook

1 Answers1