Inserting Worksheets Between Two Others - VBA

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
147
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,585
Office Version
  1. 2013
Platform
  1. Windows
Just replace After with Before
Rich (BB code):
ws.Copy Before:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@GWteB wouldn't the following shortened line work?:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
147
Office Version
  1. 365
Platform
  1. Windows
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
Fluff, thanks, that worked perfectly.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,700
Messages
5,766,010
Members
425,322
Latest member
galaxy6623top

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
Top