Update excel files data

[Abhijeet]

Hi

I have Same Format Data in excel files in one folder around about 30 files files save as Trust Numbers i.e 100.xls,148.xls like this i want to know in Column M is Update any thing or not If that column is update then Column N status is resolve other wise that status is Pending i want to know how much pending In each trust.How to do this

 

[Luke M]

Combine all the files into a single workbook, then do a countif on each sheet to see how many pendings are on each sheet, then sum the results.

 

[Abhijeet]

i have macro but that file macro goes down in last row so all files not merge.I do not know why macro goes down in last row

 

[Abhijeet]

Hi Luke M

I have this macro but i want to few changes in this macro All files Data in Same Format & Heading also Every File has 5 sheets i want merge every sheets data in to that sheet.For More than 200 rows i do not want below that data

 

[Abhijeet]

This Macro combine all files & Sheets data in to single sheet i want as per sheets Name combine all files data in that particular sheets. Please tell me how to do this

 

[Abhijeet]

Luke M
Please tell me how to combine all files data in sheet wise in this macro

 

[Abhijeet]

Please tell me how to do this

 

[Abhijeet]

This Type of file i have upload 1 file with data

 

[Abhijeet]

Hi
I have this Macro Selected folder pull data but i want Selected folder few files want to pull in this macro i want check boxes which file name check box is tick that file i want to pull in this macro.please tell me how to do this check boxes

 

[Deepak]

Hi Abhijeet,

Are u looking for something like as attached.

 

[Abhijeet]

This is also good give it me in my macro here is upload file
http://forum.chandoo.org/threads/update-excel-files-data.22461/#post-137526

 

[Deepak]

non tested...

Option Explicit

Option Compare Text

Sub merge_multiple_workbooks()

' DECLARE ALL VARIABLES AND ARRAYS

Dim fldpath As Variant
Dim fld, fil, FSO As Object
Dim WKB As Workbook
Dim wks As Worksheet
Dim shtnames()
Dim Paste
Dim j As Long, w As Long
Dim stcol As String, lastcol As String, fc As Integer
stcol = "A" ' Change the starting column of ur data

lastcol = "Z" ' Change the ending column of ur data

Dim i As Long

Set fldpath = Application.FileDialog(msoFileDialogFilePicker)
With fldpath
    .Title = "Choose the folder"
    .AllowMultiSelect = True
    .Show
    fc = .SelectedItems.Count
      If Not fc > 0 Then MsgBox "Folder Not Selected": Exit Sub
End With

' change sheet names here

shtnames = Array("Travel Qry", "Travel Confirmation", "Salary Qry", "Salary Confirmation", "Absence") '\ add or remove sheets

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.StatusBar = True
Application.StatusBar = "Please wait till Macro merge all the files"

For i = 1 To fc

Set WKB = Workbooks.Open(fldpath.SelectedItems(i))
    For j = LBound(shtnames) To UBound(shtnames)
        For Each wks In WKB.Sheets
            If wks.Name = shtnames(j) Then
                w = WKB.Sheets(shtnames(j)).Range("a65356").End(xlUp).Row
                    If w >= 2 Then
                        WKB.Sheets(shtnames(j)).Range(stcol & "2:" & lastcol & w).Copy _
                            Destination:=ThisWorkbook.Sheets(shtnames(j)).Range("a65356").End(xlUp).Offset(1, 0)
                    End If
                Exit For
            End If
        Next
    Next
WKB.Close

Next

MsgBox "Done"
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Abhijeet]

Thanks Deepak Its work