Multi process macro - save open WB, copy data to new WB, del saved...

OK so here is a step by step breakdown of what I need to happen

1- Save all open workbooks to "C:\My Documents\test\"
2- Copy all data from saved workbooks into new "Master File" (Data is only in Columns A and B, data needs to be stacked together in new file)
3- Close and delete previously saved workbooks (could be closed in original operation?)
4- Delete duplicates from new Master
5- Sort column A "A-Z"

I can get all the files to save how I want, I can remove the duplicates from the master and sort it how I want. I just can't figure out how to get the data out of the saved files into a new file and then delete the saved files.

Here are the codes I have been playing with.

This one saves them all

  For Each Workbook In Workbooks
    Workbook.SaveAs Filename:= _
        "C:\My Documents\Test\" & Workbook.Name, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
Next

This one I found to try and combine them

Option Explicit

Sub Consolidate()
'Author:     Jerry Beaucaire'
'Date:       9/15/2009     (2007 compatible)  (updated 4/29/2011)
'Summary:    Merge files in a specific folder into one master sheet (stacked)
'            Moves imported files into another folder

Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet

'Setup
    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now
    
    Set wsMaster = ThisWorkbook.Sheets("Master")    'sheet report is built into

With wsMaster
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        .UsedRange.Offset(1).EntireRow.Clear
        NR = 2
    Else
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    End If

'Path and filename (edit this section to suit)
    fPath = "C:\My Documents\test\"            'remember final \ in this string
    fPathDone = fPath & "Imported\"     'remember final \ in this string
    On Error Resume Next
        MkDir fPathDone                 'creates the completed folder if missing
    On Error GoTo 0
    fName = Dir(fPath & "*.xls*")        'listing of desired files, edit filter as desired

'Import a sheet from found files
    Do While Len(fName) > 0
        If fName <> ThisWorkbook.Name Then              'don't reopen this file accidentally
            Set wbData = Workbooks.Open(fPath & fName)  'Open file

        'This is the section to customize, replace with your own action code as needed
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
            Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            Name fPath & fName As fPathDone & fName           'move file to IMPORTED folder
        End If
        fName = Dir                                       'ready next filename
    Loop
End With

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub

I haven't gotten around to trying to get them to delete, and the remove duplicates and sort is easy enough I won't post that. I just can't figure out how to get the combine code to work for me. I feel like there is a simpler way to do it.

Hi Alex,

Here is some code that saves each open workbook and then deletes them from that directory. I didn't do any code to transfer data since it sounded from your post like you had that handled.

Sub Daniel()

Dim CurrentBook As Workbook
Dim Bookname() As String
Dim BookCount As Long
Dim i As Long

Set CurrentBook = ActiveWorkbook

' Get count of open workbooks
BookCount = 0
For Each Workbook In Workbooks
   BookCount = BookCount + 1
Next

ReDim Bookname(1 To BookCount) As String

' Save each workbook to "c:\My Documents\Temp" folder
i = 1
For Each Workbook In Workbooks
    If (Workbook.Name = CurrentBook.Name) Then GoTo 10
    Bookname(i) = Workbook.Name
    Workbook.SaveAs Filename:= _
        "C:\My Documents\Test\" & Workbook.Name, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    Workbook.Close
    i = i + 1
10 Next


' Delete each workbook in "c:\My Documents\Temp" folder
For i = 1 To BookCount
   If (Bookname(i) = "") Then GoTo 20
      Kill "c:\My Documents\Test\" & Bookname(i) & ".xls"
20 Next i

End Sub

Thanks,

Daniel