Need a Macro that does the opposite of what I currently have

[copleyr]

Hello,

I have two macros:

1) The first one go into a particular folder and pull the names of each excel file. (first 7 characters of each file in the folder) It will list them in column "A" of a spreadsheet.

2) The second macro will go into the "Defaults" tab of each of these files and copy row 70 (columns "A" through "BM") and paste it into a blank sheet.

Once I run both of these macros, I combine the information from both of the spreadsheets generated.

I now need a macro that will:

1) Look into the information generated, match the first 7 characters generated from the first macro with the excel spreadsheets in the file folder.

2) I then need it to copy and paste the information generated from the second macro back into each file. (i.e. go into the "Defaults" tab of each of these files and paste back in row 70 (columns "A" through "BM"))

Many thanks in advance!!!



Here is the first one:

Sub ListFiles()
Dim MyPathName As String
Dim MyFileName As String
Dim NumChars As Long
Dim X As Long

    NumChars = 7 'Change this to the number of characters you want to return
    MyPathName = "C:\Users\copleyr\Desktop\All/" 'Change this to the folder you want to return
    MyFileName = Dir(MyPathName)
    Do While MyFileName <> ""
        X = X + 1
        Sheet1.Cells(X, 1) = Left(MyFileName, NumChars)
        MyFileName = Dir
    Loop
End Sub

Here is the second one:

Sub Copy_dem_files() 'Ryan Copley 6.29.11
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\copleyr\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)
    Application.DisplayAlerts = False
    Sheets("Defaults").Range("A70:BM70").Copy
    Workbooks(SummarySheet).Activate
    Range("A" & X & ":BM" & X).PasteSpecial xlPasteValuesAndNumberFormats
    Range("A" & X & ":BM" & X).PasteSpecial xlPasteFormats
    Workbooks(MyFileName).Close False
    MyFileName = Dir
Application.DisplayAlerts = True
Loop
End Sub