Consolidate data from files

[Sachinaddi]

Need to copy data from 12 files named 1 to 12

all files are saved in a folder name "Files"

all file name are 1 to 12

all files having same tab name

all tabs have same colmn range "A to U" but row range are different

need to copy data from tab named "B2B" from A6 to U6

and paste in file name "combined" in same folder and tab name B2B

after pasting data need to add file name at the end of column V6 in all rows

data to be be paste from other file at the end of 1st file data paste and so on

 

[Sachinaddi]

 

[Fuji]

try

Sub test()
    Dim myDir$, fn$, i&, s$, LR&, wb As Workbook
    Const wsName$ = "B2B", r$ = "A6:U6", wbName$ = "combined"
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    fn = Dir(myDir & wbName & ".xls*")
    If fn = "" Then MsgBox "No workbook named " & wbName & " found", vbCritical: Exit Sub
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(myDir & fn)
    wb.Sheets(wsName).[a1].CurrentRegion.Offset(1).ClearContents
    For i = 1 To 12
        fn = Dir(myDir & i & ".xls*")
        If fn <> "" Then
            LR = wb.Sheets(wsName).Evaluate("max(if(a1:t100<>"""",row(1:100)))") + 1
            s = "'" & myDir & "[" & fn & "]" & wsName & "'!" & Split(r, ":")(0)
            With wb.Sheets(wsName).Cells(LR, 1).Resize(Range(r).Rows.Count, Range(r).Columns.Count)
                .Formula = "=if(" & s & "<>""""," & s & ","""")"
                .Value = .Value
                .Columns(.Columns.Count + 1) = fn
            End With
        End If
    Next
    wb.Close True
    Application.ScreenUpdating = True
End Sub

 

[Sachinaddi]

Thanks a lot i made some few updates and its worked very well ......