-
Jun 27th, 2008, 02:29 PM
#1
Thread Starter
Lively Member
[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:
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:
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:
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
-
Jun 27th, 2008, 02:33 PM
#2
Thread Starter
Lively Member
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!
-
Jun 27th, 2008, 02:38 PM
#3
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Sep 9th, 2008, 01:44 PM
#4
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|