+ Reply to Thread
Results 1 to 7 of 7

Moving worksheets into another workbook

  1. #1
    Registered User
    Join Date
    03-15-2013
    Location
    Westlake Village
    MS-Off Ver
    Excel 2010
    Posts
    11

    Moving worksheets into another workbook

    Hi there,

    I'm having a ton of trouble trying to to move the tabs from one workbook into another existing file. Both have .xlsx extensions. I've already tried the following:

    1. Removed all external links and names within the Name Managers. Didn't make a difference.
    2. Moved the tabs from each file into separate new workbooks, then tried to merge the two new files from there. Doesn't work.
    2. Saved both files with .xls extensions, then re-opened and successfully merged the tabs. The problem is that all formatting has been stripped out because .xls doesn't support a lot of the heavy formatting capabilities of .xlsx files.

    So with that, I'm out of ideas and desperately trying to avoid the need to recreate both files, which are very large and complicated (read as: I'll likely lash out irrationally if recreating the files is the only solution). Any help would be very much appreciated.

    Thanks,
    Daniel

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Moving worksheets into another workbook

    Please Login or Register  to view this content.
    Adjust the blue code to suit.
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    03-15-2013
    Location
    Westlake Village
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Moving worksheets into another workbook

    Hi Andy,

    Thank you so much for the response. I apologize for the elementary-level question, but would you help me understand if I need to change anything within this code to make it work for my purposes? For example, do I need to change the reference to "C:\Users\Serenity\Desktop\USB Ram\Book1.xlsm"? I created a file called Book1 on my desktop and changed this line to match a file-path to my desktop, but that didn't seem to work. Again, I'm so sorry for the basic VBA question...I have and will continue to study this code you provided to make it work.

    Thanks,
    Daniel

    Dim SourceBook As Excel.Workbook
    Dim Archivebook As Excel.Workbook
    Sub OpenWb()
    Set SourceBook = Workbooks(ThisWorkbook.Name)
    If IsWbOpen("Book1.xlsm") = True Then
    GoTo Skip
    End If
    Application.DisplayAlerts = False
    Set Archivebook = Workbooks.Open("C:\Users\dant\Desktop\Book1.xlsm")
    Range("A1").Select
    SourceBook.Activate
    Range("A1").Select
    Sheet21.Move Before:=Archivebook.Sheets(1)
    SourceBook.Activate
    Range("A1").Select
    Application.DisplayAlerts = True
    Skip:
    End Sub
    Function IsWbOpen(wbName As String) As Boolean
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
    If Workbooks(i).Name = wbName Then
    Set Archivebook = Workbooks.Item(i)
    Exit For
    End If
    IsWbOpen = False
    Next
    End Function

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Moving worksheets into another workbook

    Hi, Andy,

    Quote Originally Posted by deturnbull
    Both have .xlsx extensions
    Your code
    Please Login or Register  to view this content.
    Please explain how a macrofree workbook can be ThisWorkbook.

    @Daniel:
    Please use Code-Tags for displaying the code. Thank you.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    03-15-2013
    Location
    Westlake Village
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Moving worksheets into another workbook

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Moving worksheets into another workbook

    HaHoBe .......... We're writing a macro for the source book to archive the worksheets so it will have to be a .xlsm..

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Moving worksheets into another workbook

    Hi, Andy,

    quite some information missing on your first answering post then.

    BTW: no need for the workbook to be an *.xlsm, IŽd go with an AddIn (third workbook) to have the code available for other cases as well (which means that information like the names of the workbooks and the worksheet(s) should be passed as parameters into the code). Just my thoughts of when I first read the thread.

    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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