Search Column and If Cell has Text String In It Copy and Paste Data

[jbenfrancis1]

Hi, I have a master workbook and a macro that searches for a file name and opens it once found. The files being found all have a table in them with the left most column in column B but the start row changes from file to file. All files being found either have "Affiliation →" or "Line of Business →" in column b which is the start of the table.

I am trying to write a macro that will look at the active workbook and search column b for either "Affiliation → " or "Line of Business →". Once it finds the cell in column b with that text string, I want it to ctrl+shift+down and ctrl+shift+right to copy that cata. I can then finish the macro to activate my master book and paste data.

I tried this orignally but it lonly looks for "Affiliation" and doesnt account for speacial character arrow

Sub Copy()
Dim wb1 as Workbook
Dim wb2 as Workbook
wb1 = ThisWorkbook
wb2 = "Data.xlsx"
Dim AffCol As Range
Dim Aff As Range
Dim PasteCell As Range
Set AffCol = sb2.Sheet1.Range("B:B")
For Each Aff In AffCol
    If wb1.Sheet5.Range("A2") = "" Then
        Set PasteCell = wb1.Sheet5.Range("A2")
    Else
        Set PasteCell = ws1.Sheet5.Range("A1").End(xlDown).Offset(1, 0)
    End If
    If Aff = "Affiliation" Then Range(Aff.End(xlToBottom), Aff.End(xlToRight)).Copy PasteCell
Next Aff
        
End Sub

 

[Micron]

To find if a cell value/string contains a particular string, maybe use Instr function? Like
If Instr(1,Aff, "Affiliation") > 0 Then
0 means not found, anything greater than 0 is the starting position of the string that was found.

 

[Joe4]

Another important thing to keep in mind when writing code.

NEVER use reserved words like "Copy" as the name of your procedures, functions, or variables.
Reserved words are words that Excel and VBA already use for names of things like functions, methods, properties, etc.

As matter as fact, you are using it right in your procedure yourself!

    If Aff = "Affiliation" Then Range(Aff.End(xlToBottom), Aff.End(xlToRight)).Copy PasteCell

Using reserved words can cause errors and unexpected results, as Excel cannot be sure that when you use "Copy" in your code, if you are referring to the built-in "Copy" action, or your "Copy" procedure.
Best practice is to NEVER use reserved words like that.

I will often preface my procedures, functions, and variables with the word "My" if I think there is any change it may be a reserved word, i.e.

Sub MyCopy()

 

[jbenfrancis1]

I appreciate the word of advice. Sub name has been changed! Changed and used the Instrg function and it worked well. Would I just write and elseif statement for line of business?

 

[Joe4]

I appreciate the word of advice. Sub name has been changed! Changed and used the Instrg function and it worked well. Would I just write and elseif statement for line of business?

If you have more than a few options, a CASE statement would probably be cleaner.
See: MS Excel: How to use the CASE Statement (VBA)

 

[Micron]

Agree with the naming advice. Suggest you find one that you like and adopt it, more or less. I use these (albeit they're mainly for Access, but the technique is portable).
- General: Commonly used naming conventions
- MS Access Naming Conventions
Also agree that Select Case block might be better, but if there can only be 2 tests, another If line, similar to the prior one, might be good enough. Or could use Else (elseif really not applicable if only 2 tests). This is how I might write the original procedure, although it looks like sb2 is a typo, which might produce unexpected results. It also suggests that Option Explicit is not being used:

Sub CopyMyData()
Dim wb1 as Workbook, wb2 as Workbook
Dim rngAffCol As Range, rngAff As Range, rngPasteCell As Range

wb1 = ThisWorkbook
wb2 = "Data.xlsx"

Set rngAffCol = sb2.Sheet1.Range("B:B")
For Each rngAff In rngAffCol
    If wb1.Sheet5.Range("A2") = "" Then
        Set rngPasteCell = wb1.Sheet5.Range("A2")
    Else
        Set rngPasteCell = ws1.Sheet5.Range("A1").End(xlDown).Offset(1, 0)
    End If
    If rngAff = "Affiliation" Then Range(rngAff.End(xlToBottom), rngAff.End(xlToRight)).Copy PasteCell
Next rngAff
      
End Sub