+ Reply to Thread
Results 1 to 2 of 2

Copy First Sheet from Multiple Workbooks into New Workbook

  1. #1
    Registered User
    Join Date
    04-23-2020
    Location
    NYC
    MS-Off Ver
    MS Office 365 (2019)
    Posts
    8

    Question Copy First Sheet from Multiple Workbooks into New Workbook

    Hi,

    I am looking to combine the first sheet (Sheet1) in a large number of workbooks into a new workbook by selecting all of them at one time. Also, I'd like to rename each Sheet1 copied over to the name of the excel file (trimmed to the last 8 digits).

    I'd sketched out the macro so far below just need help finishing it! Thanks so much!

    Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook

    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

    If (vbBoolean <> VarType(fnameList)) Then

    If (UBound(fnameList) > 0) Then
    countFiles = 0
    countSheets = 0

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set wbkCurBook = ActiveWorkbook

    For Each fnameCurFile In fnameList
    countFiles = countFiles + 1

    Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

    For Each wksCurSheet In wbkSrcBook.Sheets
    countSheets = countSheets + 1
    wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
    wbkCurBook.Sheets(wbkCurBook.Sheets.Count).Name = Right$(wbkSrcBook.Name, InStrRev(wbkSrcBook.Name, ".") - 8)

    Next

    wbkSrcBook.Close SaveChanges:=False

    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
    End If

    Else
    MsgBox "No files selected", Title:="Merge Excel files"
    End If
    End Sub

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Copy First Sheet from Multiple Workbooks into New Workbook

    Possibly...
    Please Login or Register  to view this content.
    Last edited by dangelor; 08-12-2021 at 01:49 PM. Reason: Changed sheet name generation

+ 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] VBA to copy sheet (i) of workbook (z) to sheet (j) in new workbooks (n) with values only
    By megaheinz in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-30-2021, 10:20 AM
  2. Copy First Sheet of Multiple Workbooks in Folder to One Workbook
    By cavalieregiuseppe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2021, 06:22 AM
  3. Replies: 9
    Last Post: 02-13-2015, 05:12 AM
  4. How to copy a sheet from one workbook as a sheet onto multiple workbooks
    By hld142 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2014, 10:11 AM
  5. vba to copy from multiple workbooks to a summary sheet in another workbook.
    By lisadykstra in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-22-2014, 03:36 PM
  6. Copy Sheet from one Workbook to Multiple Closed Workbooks
    By sflexi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2013, 10:01 AM
  7. [SOLVED] Open Multiple Workbooks, Record names, Copy paste to Active Workbook, Close the Workbooks
    By vba_madness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 06:09 AM

Tags for this Thread

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