Vba help. copying data from several excel files and appending to one sheet

[teefi]

hello all!
I'm in need of some assistance please.
I regularly copy data from several excel files (one sheet) into one master sheet.
i have been looking to autonate this via a macro but am stuck!
the structure of the macro i believe to be:
1 Select files
2 create new file (excel)
3 copy and append data from each file into the new file ( variable)

all the files will have the same number of columns but will have unknown amount of rows.

i have seen variations of this but can't apply to what my issue.

can anyone help?

thanks in advance

 

[sweeneytime]

This is set up for .xml files, just change the extenstion as you need, along with the other variable that apply to you.

'==========================
' Procedure  : FileLoop
' Date       : 18/11/12
' Author     :
' Phone      :
' Description: Loop through each file in folder
'--------------------------
Sub FileLoop()
    
    Dim oWbk As Workbook
    Dim sFil As String
    Dim sPath As String
    Dim OpenWB As Workbook
    Dim CodeWB As Workbook
    Dim SalesWB As Workbook
    Dim LR As Long
    Dim LR2 As Long
    
    Set CodeWB = ActiveWorkbook
    
    'Folder Location
    sPath = "O:\ORC Finance\Macros & SQL & Access\Macros\XML\Web Store Files\"
    
    ChDir sPath
    
    'Find only xml files
    sFil = Dir("*.xml") 'change or add formats
        
        'Loop through all files in foler
        Do While sFil <> ""
        
        'Delete and Enter New import sheet - as re-using xml maps is problmeatic
        Application.DisplayAlerts = False
        Sheets("Import Data").Delete
        Application.DisplayAlerts = True
        
        Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Import Data"
        
        ActiveWorkbook.XmlImport URL:= _
        sPath & "\" & sFil _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")
        
            
        LR = Range("A" & Rows.Count).End(xlUp).Row
        
        If LR > 1 Then
            Range("A2:A" & LR).EntireRow.Copy
        Else
            Range("A1:A" & LR).EntireRow.Copy
        End If
        
        
        'Paste sales lines in Data sheet in the codewb
        Sheets("Data").Select
        LR2 = Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & LR2 + 1).EntireRow.Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
 

        sFil = Dir
    Loop
End Sub

 

[teefi]

thanks for this.
However, even with my edits i can't get it to work. i am still a novice in vba so don't really know where i am going wrong!
i have added the location of the folder but it doesnt open any files.it just selectsba row of cells in a blanksheet where the code is.

 

[sweeneytime]

post the code, in tags, with your changes