Inserting Worksheets Between Two Others - VBA

[excelbytes]

I am using the following VBA code to pull in the worksheets from various workbooks in a specific folder into one workbook. It works perfectly. The workbook that the files end up in has two worksheets that are blank called "First" and "Last". The reason is, that I have formulas in one worksheet on sums all the values in the worksheets that are between those two worksheets (e.g. =IFERROR(IF($A4=$A$1,SUM(First:Last!$E$6),""),"")).

What I would like to know is, how do I modify the code below so that when the worksheets are pulled into it, they end up between the First and Last worksheet, instead of after the Last worksheet?

Sub CopyFiles()

Dim Path As String
Path = "C:\Users\mremp\OneDrive\Documents\Excel Stuff\Working\Dave\"

Dim FileName As String
FileName = Dir(Path & "*.xlsx")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop

Worksheets(1).Delete

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Worksheets("Classification Summary").Select

Range("A1").Select

End Sub

 

[GWteB]

Just replace After with Before

ws.Copy Before:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

 

[johnnyL]

@GWteB wouldn't the following shortened line work?:

ws.Copy Before:=ThisWorkbook.Sheets(Sheets.Count)

 

[Fluff]

My take on this

   ws.Copy Before:=ThisWorkbook.Sheets("Last")

@johnnyL that will count the number of sheets in the active workbook, not in ThisWorkbook

 

[excelbytes]

My take on this

   ws.Copy Before:=ThisWorkbook.Sheets("Last")

@johnnyL that will count the number of sheets in the active workbook, not in ThisWorkbook

Fluff, thanks, that worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.