How to import one sheet from Closed file to open sheet

[Fawn Marie]

I have a "Master" file with many sheets for each production week. I am trying to copy from the sheet of raw data file to the "master" file. For example "raw data" workbook is called "May 3-9" and sheet in the workbook is called "May 3-9". The below code works fine for copying it but it creates a new sheet. I would like it to insert the into a current sheet (May 3-9) of Master. I also want it to update once the raw data file is updated (closed file). I am VERY new to VBA and I am not thinking that I will ever figure this out.



Sub Importsheet()
Dim Importsheet As Worksheet
Sheets.Add Type:= "C:\Users\Fawn\SkyDrive\External Hardware\NDT\Reports\OMA Agent Performance Management\Ready 2015\May 3-9.xls"
End Sub

[Fawn Marie]

Okay so I came up with a new code


Option Explicit


Set wbk = Workbooks.Open("C:\Users\Fawn\SkyDrive\External Hardware\NDT\Reports\OMA Agent Performance Management\Ready 2015\May 3-9.xls")

Dim x As Variant
x = wbk.Worksheets("May 3-9").Range("a1").Value

Call wbk.Worksheets("May 3-9").Range("a1:t200").Copy
Call ThisWorkbook.Worksheets("May 3-9").Range("a1").PasteSpecial(xlPasteValues)
Application.CutCopyMode = False

Call wbk.Close(False)

End Sub


But now my dilemma is this: I have 52 weeks of tabs (May 3-9, May 10-16, May 17-23....etc). How can I create one module in VBA that copies the raw data file of the same name as tab in the master file. All raw data files are in the same folder C:\Users\Fawn\SkyDrive\External Hardware\NDT\Reports\OMA Agent Performance Management\Ready 2015\.