Hello,
Perhaps I'm going about this is a convoluted way:
- I want to be able to insert specifically named macro-enabled worksheets into all excel files in a particular folder. For now, lets call them sheet1 and sheet2. (The excel files that are receiving new sheets may or may not be macro-enabled)
- I, then need to run a macro within those newly updated excel files.
- I found VBA to insert an active sheet across all worksheets in a folder, but I want to be able to reference sheets the will be inserted.
- Can this code be edited to do that?
Sub CopySheet()
Const strFldrPath As String = "C:\Test Folder\" 'Change this path to desired. Be sure to include the ending \
Dim CurrentFile As String, FileExt As String, wb As Workbook, wsActive As Worksheet, ThisExt As String
Set wsActive = ActiveWorkbook.ActiveSheet
If InStr(1, ActiveWorkbook.Name, ".", vbTextCompare) > 0 Then
ThisExt = StrReverse(Left(StrReverse(ActiveWorkbook.Name), InStr(1, StrReverse(ActiveWorkbook.Name), ".", vbTextCompare)))
Else
ThisExt = ".xlsx"
End If
CurrentFile = Dir(strFldrPath)
While CurrentFile <> vbNullString
FileExt = StrReverse(Left(StrReverse(CurrentFile), InStr(1, StrReverse(CurrentFile), ".", vbTextCompare)))
If LCase(ThisExt) = ".xls" Then
If LCase(FileExt) = ".xls" Or LCase(FileExt) = ".xlsx" Or LCase(FileExt) = ".xlsm" Then
Set wb = Workbooks.Open(Filename:=strFldrPath & CurrentFile)
wsActive.Copy Before:=wb.Sheets(1)
wb.Close True
End If
Else
If LCase(FileExt) = ".xlsx" Or LCase(FileExt) = ".xlsm" Then
Set wb = Workbooks.Open(Filename:=strFldrPath & CurrentFile)
wsActive.Copy Before:=wb.Sheets(1)
wb.Close True
End If
End If
CurrentFile = Dir()
Wend
End Sub
Thanks,
Karim
Bookmarks