Code to copy range from multiple worksheets into a master worksheet

[dbuzbee]

I am working on a workload tracking workbook for summarizing employee hours on key projects. The workbook contains multiple worksheets for reporting as well as individual employee worksheets for tracking. Each employee worksheet has an identical structure but the number of worksheets will vary as employees are added or leave the department. For each employee worksheet, I want to copy the same range (A9:R100) to one master worksheet (Summary) which will then be used for PivotTables and other reports.

What would be the best way to copy from the first employee worksheet (not the first one in the workbook), paste the range into Summary, and then move to the next worksheet and repeat until the last employee worksheet? The employee worksheets are currently between the StartHere and EndHere worksheets.

I'm trying a Do Until Loop but I get an "Object variable or With block variable not set" error on the line "ws.Range...."

Perhaps this should be a For-Next Loop or handled in some other way?

Sub Import()
'
Dim wsDest As Range
Dim ws As Worksheet

' Go to starting sheet to import
Sheets("StartHere").Select
Worksheets(ActiveSheet.Index + 1).Select

'Add Employee Details
Do Until ActiveSheet.Name = "EndHere"
With Sheets("Summary")
Set wsDest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
'Copy the range and paste into next cell in Summary
ws.Range("A9:R100").Copy Destination:=wsDest

Worksheets(ActiveSheet.Index + 1).Select

Loop
End Sub

Thanks in advance for your time

 

[AlphaFrog]

Sub Import()
    '
    Dim i As Long
    
    'Add Employee Details
    With Sheets("Summary")
        For i = Sheets("StartHere").Index + 1 To Sheets("EndHere").Index - 1
            Sheets(i).Range("A9:R100").Copy Destination:=.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        Next i
    End With
End Sub

 

[dbuzbee]

Thank you for the quick response-looks good! Unfortunately, nothing happens when I run this code. When I step into it, the macro doesn't run past the Sub name. Curious