Search workbook for keyword - copy column(s) to other workbook (Extremely New to VBA)

[tolocdn]

Hello all,

(Very first post and as mentioned extremely new to VBA - which programmers tell me will melt my brain )

I have some code already that works to open a workbook and search for a keyword and copy the cells next to that word. Now I have a need to search designated workbooks for a keyword and copy the entire column of data (or last cell in that column that contains a value).

Example:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Speed 1[/TD]
[TD]Condition Count[/TD]
[TD]Speed 2[/TD]
[TD]Condition Count[/TD]
[TD]Total Counts[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]456[/TD]
[TD]4[/TD]
[TD]654[/TD]
[TD]6[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]789[/TD]
[TD]6[/TD]
[TD]345[/TD]
[TD]7[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]234[/TD]
[TD]4[/TD]
[TD]345[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD]1379[/TD]
[TD][/TD]
[TD]1344[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]










I would have to search the workbook (which is really only 1 sheet in csv format) for the word Speed and then return the last cell values (1379 and 1344 in this case). The book it is searching can see anywhere from 3-8 instances of the keyword.

Thanks in advance!

Dave

 

[tolocdn]

AlphaFrog - thanks once again!

I used a hybrid solution and went as follows:

Ccnt = Ccnt + 1
If Ccnt > RepTot Then GoTo ImpACD

I'll get the hang of this yet!

 

[tolocdn]

Uh oh! Ran into a small issue. How do I get .Find to STOP when it hits the bottom of a sheet and NOT wrap around again to the top. One of the reports that I read in has multiple sections, and I start the search from the section title. Below is the code that I search with, the issue now becomes that the name I search for does not exist in one section "CuSat" but does exist above it. Find is looping back to the top and returning results that don't apply.

Workbooks.Open FileName:=File1    Cells.Find(What:="CuSat", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
        
       
    Cells.Find(What:=NameCell, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
                           
    ActiveCell.Offset(, 1).Resize(1, 6).Copy

 

[AlphaFrog]

This searches rows 4 to the last row. (excludes rows 1:3)

Rows("4:" & Rows.Count).Find(What:=NameCell, LookIn:=xlFormulas, _

You can omit the After:=ActiveCell argument.

 

[tolocdn]

AlphaFrog if I leave the ActiveCell in will it start in the 4th row AFTER the present active cell (which in the first part of the code should be "CuSat")? Only reason I ask is because the report is dynamic and can contain various numbers of titles and names.

 

[AlphaFrog]

if I leave the ActiveCell in will it start in the 4th row AFTER the present active cell

If the ActiveCell is not within the search range, it would throw an error.

If omitted, it defaults to searching after the 1st cell of the search range (in the above example that would be cell A4)

 

[tolocdn]

Ok I did a little bit of code juggling and mind melting and did the following that worked:

Workbooks.Open FileName:=File1    
    Set Foundr = Cells.Find(What:="CuSat", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
                         
    If Not Foundr Is Nothing Then
         Foundr1 = Foundr.Row
    End If
        
    Set Foundr2 = Rows(Foundr1 & ":" & Rows.Count).Find(What:=NameCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
                           
    If Not Foundr2 Is Nothing Then
                           
    Foundr2.Offset(, 1).Resize(1, 6).Copy
    ActiveWindow.Close
    Sheets("DataImport").Select
    Range("B16").Select
    ActiveSheet.Paste
    
    Else
    
    ActiveWindow.Close
    Sheets("DataImport").Select
    
    End If

Basically - Open the file - search for CuSat, return the row number it is in, use that Row number as the start and last row as the end for the new confined Name search, copy the 6 cells to the right if found, close the book and paste into present book OR if nothing found, close the book and go to present sheet.