Consolidation of timesheets required

[J Josh]

Hello,

I am having many timesheets say around 60 in H:\JJ\Charge Out\Mar 2018\Individual TS - with same format. Every individual is sending me Time sheets with naming convention as their name Eg. Adithya TS , Ravi TS, Megha TS etc. I need to consolidate the data from those sheets to one sheet say "Compilation TS".
Please note each Time sheet is having same format. Please find the time sheet format below

Site	Discipline	Project Number	SRF No.	Project Title	Employee name	Stage	Budgeted	Non-Budgeted	Total
GES	NA	124	NA	KPI Reports	Adithya	E	-	-	-
GES	NA	155	NA	GES-Technical Network	Adithya	E	-	-	-

<colgroup><col><col><col><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

Now in the Individual TS there are many project numbers say 200 amongst which each individual will charge only on 4 to 5 projects. So the Total column will contain numbers only in few rows (project numbers each individual will charge on).

I want to consolidate all the Time sheets - i want to pull all data from multiple workbooks to one workbooks that too in one sheet.
Can anyone help me?

 

[mumps]

This macro assumes that the timesheets in your folder are the only files in that folder and that they all have an "xlsx" extension.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wsDest As Worksheet
    Set wsDest = ThisWorkbook.Sheets("Compilation TS")
    Dim wkbSource As Workbook
    Dim LastRow As Long
    Dim ws As Worksheet
    Const strPath As String = "H:\JJ\Charge Out\Mar 2018\Individual TS\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            For Each ws In .Sheets
                If ws.Name Like "*TS*" Then
                    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    ws.Range("A1:J" & LastRow).AutoFilter Field:=10, Criteria1:="<>"
                    ws.Range("A2:J" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1, 0)
                    Exit For
                End If
            Next ws
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[J Josh]

hello,

Set wsDest = ThisWorkbook.Sheets("Compilation TS") - I am getting an error in this..once i click on debug..this line is yellow highlighted but i have saved the excel file as Compilation TS only and this workbook is Macro enabled.

 

[mumps]

Do you have a sheet named "Compilation TS" in your destination workbook?

 

[J Josh]

hi
Do you want to say - i must have Compilation TS in the given path only i.e H:\JJ\Charge Out\Mar 2018\Individual TS\

 

[mumps]

In you original post you said:

H:\JJ\Charge Out\Mar 2018\Individual TS

This tells me that your source files are saved in a folder named "Individual TS". You also said:

I need to consolidate the data from those sheets to one sheet say "Compilation TS".

This tells me that you have a worksheet named "Individual TS" in your destination workbook. This is the sheet to which the data will be copied. If you don't have a worksheet named "Individual TS" in your destination workbook, the macro will generate an error.

 

[J Josh]

The path "H:\JJ\Charge Out\Mar 2018\Individual TS" has individual TS i.e Akash TS, Ajay TS and all. Please note Individual TS is name of the folder in which all TS are saved and Compilation TS is the sheet in which i want to collate the data. this Compilation TS is in located in H:\JJ\Charge Out\Mar 2018\. I hope this clarifies that in Mar 2018 folder Individual TS (folder) and Compilation TS (excel sheet) is located.

 

[mumps]

Perhaps you might be confusing "Compilation TS" as a workbook name versus "Compilation TS" as a sheet name. It sounds like your workbook is saved under the name "Compilation TS" but does this workbook also have a worksheet in it named "Compilation TS"?