Hi nagajan,
You want to "run the directory", interestingly - yours is the third I've encountered this week - so here's a working version that you should be able to adapt with your own book and sheet names:
Sub PasteBase(): Dim wb As Workbook, ws As Worksheet, wd As Worksheet, r As Long
Dim S As String, P As String, U As String: P = ActiveWorkbook.Path & "\": U = Dir(P)
'When running a directory you need to specify the first in the folder
'You get that by setting the path & backslash i.e "C:\Documents and Settings\"
'Unless you know the Path for these files you'll need to have one of them up at invocation
For Each wb In Workbooks 'If "Master" is open then Set receiving sheet
If wb.Name Like "Master.xls*" Then GoTo SetDB
Next: Workbooks.Open Filename:=P & "Database" 'Else open "Database"
SetDB: Set wd = Workbooks("Database").Sheets("Direct Personel"): r = 3
SetaBook:
If U Like "Database.xl*" Then GoTo GetaBook
If InStr(1, U, ".xl") = 0 Then GoTo GetaBook
If InStr(1, U, "Report") Then 'this only if there are others in the folder
Workbooks.Open Filename:=P & U, UpdateLinks:=0
Set wb = ActiveWorkbook: Set ws = wb.Sheets("Direct Personel")
ws.Range("A3:D59").Copy wd.Cells(r, 1): wb.Close SaveChanges:=False
r = r + 59: End If
GetaBook: U = Dir() 'Get another book
If U = "" Then Exit Sub
GoTo SetaBook: End Sub
Bookmarks