Wednesday, November 6, 2013

Updating Master File Using VBA

VBA Code for updating Master file sheet by different name from other other workbooks with same sheet name under  folder( with same sheet name)




Option Explicit
Dim fso As FileSystemObject
Dim fl As file
Dim fldr As Folder, i As Integer
Dim wb As Workbook, rowcount As Integer, rowcount1 As Integer
Sub updateSheet()
On Error Resume Next
Application.ScreenUpdating = False
Set fso = New FileSystemObject
For i = 4 To ThisWorkbook.Sheets.Count
     
    If (fso.FolderExists(ThisWorkbook.Path & "\" & Sheets(i).Name) = True) Then
     
      Set fldr = fso.GetFolder(ThisWorkbook.Path & "\" & Sheets(i).Name & "\")
      rowcount = ThisWorkbook.Sheets(i).Range("a2").End(xlDown).Row
   
        For Each fl In fldr.Files
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & Sheets(i).Name & "\" & fl.Name)
            rowcount1 = wb.Sheets(1).Range("A2").End(xlDown).Row
         
            wb.Sheets(1).Range("A2:T" & rowcount1).Copy Destination:=ThisWorkbook.Sheets(i).Range("A" & rowcount)
            wb.Close
            Set wb = Nothing
        Next
    End If
Next
End Sub

No comments:

Post a Comment