Copying a tab from the active workbook and pasting in another workbook as a new tab.

[smartpat19]

I am trying to add this template to every excel file in a folder.

I have the loop down to run the macro on every excel file in the folder.

however, I cant seem to get the macro down to copy the tab and paste the tab.

I appreciate all your help, I'm new at this!

---

Sub LoopAllExcelFilesInFolder()

'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim NewSheet As Worksheet

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xls"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)

'macro
Set NewSheet = Worksheets("New Executive Summary")
NewSheet.Copy
Sheets.Add.Name = "NewNew Executive Summary"

NewSheet.Paste


'Save and Close Workbook
wb.Close SaveChanges:=True

'Get next file name
myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"

ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

Here is your problems area:

Set NewSheet = Worksheets("New Executive Summary")
 NewSheet.Copy
 Sheets.Add.Name = "NewNew Executive Summary"
 NewSheet.Paste

when you use the syntax 'NewSheet.Copy' without a 'Before:=" or "After:=" parameter, VBA automatically creates a new workbook with a copy of the sheet as Sheet 1 in that new workbook. For the Sheet.Copy method, you do not need to use the 'Paste' command. Here is one way to copy the sheet into the same workbook as a new sheet.

Set NewSheet = Worksheets("New Executive Summary")
 NewSheet.Copy After:=Sheets(Sheets.Count)
 ActiveSheet.Name = "NewNew Executive Summary"

 

[smartpat19]

JLGWhiz, Thanks for the reply! your code works and It creates a new tab in each workbook but is copying from within the same workbook.

I have a "template" for the new Executive summary that I created and I want to paste this in everyone's excel sheet that tracks all our projects. The sheet is in its own, separate workbook. How do I copy that sheet over?

Thanks again for your help! I really appreciate it!

 

[JLGWhiz]

Include the workbook name (or an object variable for the workbook) in the code.

Dim wb1 As Workbook, wb2 As Workbook, NewSheet As Worksheet
Set wb1 = ThisWorkbook
Set wb2 = Workbooks(2) 'Use wb name and file estension where the index 2 appears.
Set NewSheet = Worksheets("New Executive Summary")
 NewSheet.Copy After:=wb2.Sheets(Sheets.Count)
 ActiveSheet.Name = "NewNew Executive Summary"

 

[smartpat19]

Thank you JLQWhiz. It's working now!

 

[JLGWhiz]

Thank you JLQWhiz. It's working now!

You're welcome,
regards, JLG