+ Reply to Thread
Results 1 to 1 of 1

VBA Copy macro-enabled worksheets to all workbooks in folder

Hybrid View

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    11

    VBA Copy macro-enabled worksheets to all workbooks in folder

    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
    Last edited by Kahmed2R; 07-30-2013 at 10:09 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Saving Macro-Enabled Workbooks
    By Paschen in forum Excel General
    Replies: 2
    Last Post: 03-11-2013, 11:29 AM
  2. [SOLVED] Macro to Saving Workbook Sheets as Separate Macro-Enabled Workbooks
    By puppetpalace in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2013, 01:58 PM
  3. Replies: 0
    Last Post: 08-07-2012, 02:19 PM
  4. create a compressed folder or zipped folder then copy workbooks to it.
    By Ironman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-04-2012, 03:56 PM
  5. Macro - Copy 3 cells from all workbooks in a folder
    By misterzr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2010, 09:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1