Need help finishing a loop

[copleyr]

Hello all:

The following code will go into multiple excel templates in a designated folder path, copy a row out of the "Defaults" tab, and paste it into a blank spreadsheet. Ive been getting errors when trying to change the code to do 3 things:

1) Instead of it going into just a Sheet called "Defaults", I need it to go in every Sheet in the Workbook.
2) Instead of it copying just one range in the sheet: "Range("A70:CO70").Copy", I need it to look for a unique word in one cell in column "A", Called "SBW". (There will only be one of these instances in column "A") Then I need it to copy the very next row Columns A through M.
3) Instead of it pasting into a new workbook, I'd like for it to paste into the tool that's currently open to use the macro tool.

Thanks in advance if you're able to help!

Sub Copy_files()

Dim MyPathName As String

Dim MyFileName As String

Dim NumChars As Long

Dim X As Long

Dim SummarySheet As String


Workbooks.Add

SummarySheet = ActiveWorkbook.Name

MyPathName = "C:\Users\xxxxx\Desktop\All/" 'Change this to the folder and filetypes you want to return

MyFileName = Dir(MyPathName)

X = 0

Do While MyFileName <> ""

    X = X + 1


    Workbooks.Open MyPathName & MyFileName, False

    Application.DisplayAlerts = False

    Sheets("Defaults").Range("A70:CO70").Copy

    Workbooks(SummarySheet).Activate

    Range("A" & X & ":CO" & X).PasteSpecial xlPasteValuesAndNumberFormats

    Range("A" & X & ":CO" & X).PasteSpecial xlPasteFormats

    Workbooks(MyFileName).Close False


    MyFileName = Dir

Application.DisplayAlerts = True

Loop

End Sub

One note: The term "SBW" can be in multiple rows, hence the need for a "find" function in column "A"

 

[copleyr]

What I meant to say in the one note is that "SBW" can be found in different rows in each excel template, hence the need for a "find" function in column "A"...