Copy data paste into new sheet and close

[meakashgfx]

Hi,

I'm having a problem here. In a folder I have 100 employees excel file and a Master Attendance Excel.
I want 100 employees data in Master File.

I need the following VBA:

1. Open employees file copy a Range then close.
2. Paste the data in to master sheet where employee name match.(employee name can be found in "A1" or file name)
3. loop 1 and 2

Please see this screenshot for clear the concept: Screenshot by Lightshot

It will be helpful for me.

Thanks in Advance!

 

[mrmmickle1]

Try something like this:

[COLOR=#0000ff]Sub[/COLOR] EditDirectory()

   [COLOR=#0000ff] Dim [/COLOR]path      [COLOR=#0000ff]As String[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] ThisWB    [COLOR=#0000ff]As String[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]Filename  [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]Wkb       [COLOR=#0000ff]As [/COLOR]Workbook
[COLOR=#0000ff]    Dim[/COLOR] lastR     [COLOR=#0000ff]As Long[/COLOR]
 
    Application.EnableEvents = [COLOR=#0000ff]False[/COLOR]
    Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR]

    ThisWB = ActiveWorkbook.Name
    path = "[COLOR=#ff0000]YOUR FILE DIRECTORY[/COLOR]"
    Filename = Dir(path & "\*.csv", vbNormal)
 [COLOR=#0000ff]   If[/COLOR] Len(Filename) = 0 [COLOR=#0000ff]Then Exit Sub[/COLOR]
  [COLOR=#0000ff]  Do Until[/COLOR] Filename = vbNullString
     [COLOR=#0000ff]   If Not[/COLOR] Filename = ThisWB [COLOR=#0000ff]Then[/COLOR]
            [COLOR=#0000ff]Set[/COLOR] Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
                Range("[COLOR=#ff0000]YOUR RANGE[/COLOR]").Copy
                Windows(ThisWB).Activate
                Range("[COLOR=#ff0000]YOUR RANGE[/COLOR]").PasteSpecial
            Wkb.Close[COLOR=#0000ff] True [/COLOR][COLOR=#008000]'Change to False if you do not want to save changes[/COLOR]
       [COLOR=#0000ff] End If[/COLOR]
        Filename = Dir()
  [COLOR=#0000ff]  Loop
    [/COLOR]
    Application.EnableEvents = [COLOR=#0000ff]True[/COLOR]
    Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR]
    
    MsgBox "File editing is now complete", vbInformation, "MRMMICKLE1 File Editor"

[COLOR=#0000FF]End Sub[/COLOR]

 

[meakashgfx]

Hi mrmmickle1

Thanks you for your response. but I want to find the range when I paste into master file. If file name find the table name in master file then offset 3 cell and then paste.

Please help me if you can.