Copy to Overall worksheet

[JoeK210185]

Hello all.

I have 16 people all who have their own workbooks.

They capture data on sheet 1 and it goes to sheet 2.

What I need to do is, on an overall workbook, be able to create a VBA code or a macro to copy from sheet 2 of every tab, and paste it into this new workbook.

I need to copy person1 say could be row b:f
person 2 could be row b:z
I need them to copy just the rows with actual data in.

If anyone could help me or guide me that would be fantastic.

 

[JoeK210185]

Anyone?

 

[aravindhan_31]

try this. It consolidates data from all the files that are saved in "D:\Macros\Tracker\" and the sheet name called "Productivity'

Sub CreateDatedReport_completed()
Dim wbNew As Workbook
Dim wsRpt As Worksheet:     Set wsRpt = ThisWorkbook.Sheets("Sheet1") ' ' Change the sheet name
Dim NR As Long
Dim LR As Long
Dim fPath As String:        fPath = "D:\Macros\Tracker\"    ' Change the path don't forget the final \
Dim fName As String
'Option to clear existing report

        NR = Range("A" & Rows.Count).End(xlUp).Row + 1

'Start import loop
    Application.ScreenUpdating = False
    fName = Dir(fPath & "*.xls")
    
    Do While Len(fName) > 0
      'open file
        Set wbNew = Workbooks.Open(fPath & fName)
      'apply autofilter
      Sheets("Productivity").Select ' Change the destination sht name            LR = Range("A" & Rows.Count).End(xlUp).Row
            If LR > 1 Then _
    Range("A2:Az" & LR).Select ' Change the Range that you would like to copy
                 Selection.Copy
                wsRpt.Range("A" & NR).PasteSpecial xlPasteValues
      
        wbNew.Activate
      'close
        wbNew.Close False
        
      'next loop
        NR = Range("A" & Rows.Count).End(xlUp).Row + 1
        fName = Dir
    Loop
Application.ScreenUpdating = True
End Sub