Inserting Worksheets Between Two Others - VBA

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
245
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Just replace After with Before
Rich (BB code):
ws.Copy Before:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
 
Upvote 0
@GWteB wouldn't the following shortened line work?:

VBA Code:
ws.Copy Before:=ThisWorkbook.Sheets(Sheets.Count)
 
Upvote 0
My take on this
VBA Code:
   ws.Copy Before:=ThisWorkbook.Sheets("Last")
@johnnyL that will count the number of sheets in the active workbook, not in ThisWorkbook ;)
 
Last edited:
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top