I need a bit of help as my vba skills are pretty much non existant.
I have a spreadsheet that has multiple tabs and I need to copy 2 of the tabs into all of the spreadsheets that are within the same folder as the opened excel file.
Someone has already enquired about a similar application, but the code doesn't do exactly what I'm looking for.
https://www.mrexcel.com/forum/excel-questions/399744-copy-worksheet-multiple-workbooks.html
If I edit the above code, I can copy a single tab into a specified folder.
I would like to copy two tabs into the same folder as the opened workbook. I'm just not sure how to specify them.
I work off multiple projects, so this spreadsheet will be duplicated and stored in different folders.
Another issue with the current code is that it doesn't overwrite the worksheets if they already exist in the destination workbooks.
Does anyone know how to modify the above code to suit?
Any help would be greatly appreciated.
I have a spreadsheet that has multiple tabs and I need to copy 2 of the tabs into all of the spreadsheets that are within the same folder as the opened excel file.
Someone has already enquired about a similar application, but the code doesn't do exactly what I'm looking for.
https://www.mrexcel.com/forum/excel-questions/399744-copy-worksheet-multiple-workbooks.html
Code:
Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook
'Worksheet in active workbook to be copied as a new sheet to the 160 workbooks
Set sourceSheet = ActiveWorkbook.Worksheets("Sheet1")
'Folder containing the 160 workbooks
folder = "F:\temp\excel\"
filename = Dir(folder & "*.xls", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
If I edit the above code, I can copy a single tab into a specified folder.
I would like to copy two tabs into the same folder as the opened workbook. I'm just not sure how to specify them.
I work off multiple projects, so this spreadsheet will be duplicated and stored in different folders.
Another issue with the current code is that it doesn't overwrite the worksheets if they already exist in the destination workbooks.
Does anyone know how to modify the above code to suit?
Any help would be greatly appreciated.