Results 1 to 4 of 4

Thread: [RESOLVED] [Excel] Moving a Sheet To The End of Another Workbook

  1. #1

    Thread Starter
    Lively Member Vanquish2888's Avatar
    Join Date
    Sep 2007
    Posts
    104

    Resolved [RESOLVED] [Excel] Moving a Sheet To The End of Another Workbook

    Hello yet again!

    I am trying to move several sheets to the end of a separate workbook. I have the code working like this:
    vb Code:
    1. ActiveSheet.Move Before:=wb2copyTo.Sheets(wb2copyTo.Sheets.Count)

    I tried recording a macro and selcting (Move to end) in the list box when you move sheets to another workbook manually. The code looks like this:
    vb Code:
    1. Sheets(Array("Sheet1", "Sheet2")).Move Before:=Workbooks("Book1").Sheets(4)
    In this case the workbook I moved to only contained 3 sheets. So I tried this with my code:
    vb Code:
    1. ActiveSheet.Move Before:=wb2copyTo.Sheets(wb2copyTo.Sheets.Count + 1)
    But, a error msg is produced "Subscript Out of Range".
    Thus I have come to you guys... any advice? I need to be able to move to the end and it is not always the same number of sheets in the workbook I am moving to. I also tried using After:= but Excel doesn't recognize that. Thanks a bunch in advance!!

    ~AJ

  2. #2

    Thread Starter
    Lively Member Vanquish2888's Avatar
    Join Date
    Sep 2007
    Posts
    104

    Re: [Excel] Moving a Sheet To The End of Another Workbook

    Whoops.... false alarm! I must have mispelled 'after' last time I tried it... I just tried to double check it and it worked!

  3. #3
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [RESOLVED] [Excel] Moving a Sheet To The End of Another Workbook

    Its ok to leave this as it may be helpful to someone searching on how to move a sheet.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4
    New Member
    Join Date
    Sep 2008
    Location
    Toronto, Canada
    Posts
    1

    Re: [RESOLVED] [Excel] Moving a Sheet To The End of Another Workbook

    Hi all,

    I am new to the forum and almost as new to VBA so please bear with me.

    I am trying to create a macro for something similar to this (move the current worksheet to another workbook), but there is a twist. The target workbook's name will change each month and year (ex. "September Patients 2008", "October Patients 2008")

    So far I have been able to work around this by using:

    Code:
    Workbooks.Open("C:\Documents and Settings\MGravely\Application Data\Microsoft\Internet Explorer\Quick Launch\Shortcut to Patients 2008.xls.lnk")
    ...basically just specifying the path to a shortcut which I manually redirect at the beginning of each month by inputting the current month's filename.

    However, this does not work for moving the worksheet as this (butchered) code I attempted fails me:

    Code:
    ' Move current worksheet to this month's file
        ActiveSheet.Move Before:=Workbooks(Workbooks.Open("C:\Documents and Settings\MGravely\Application Data\Microsoft\Internet Explorer\Quick Launch\Shortcut to Patients 2008.xls.lnk")).Sheets(1)
    Can someone tell me a way I might accomplish this by either

    (a) Specifying the target file using a variable which I could ammend to be named based on this month and year's date (which I am too new to know how to do, and have had little luck researching)

    OR

    (b) continuing to work around the issue by (i) opening the workbook using Workbooks.Open and the shortcut path (above) and then (ii) specifying that the active worksheet be moved to this target given that it would be the only active workbook that will match the wildcarded name "* Patients 2008.xls"

    Any help or advice would be greatly appreciated. Thanks in advance.

Posting Permissions

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



Click Here to Expand Forum to Full Width