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
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