Returning to Main Tab
Posted by Del on April 20, 2000 7:58 AM
I have a problem,
I can't get this macro to return to the "Main" tab when it has moved open worksheets to this workbook (SK Compiler).
It always stays (I think) on the last tab it has moved from another workbook.
It's probably really simple, but I can't do it!
Please help, Thanks in advance.
Del.
(Below is my macro for this)
Do Until Empty
Application.ScreenUpdating = False
ActiveWindow.ActivateNext
ActiveSheet.Select
ActiveSheet.Move After:=Workbooks("SK Compiler.xls").Sheets(2)
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.ActivateNext
Application.DisplayAlerts = False
ActiveWindow.Close
If ActiveWorkbook.Name = "SK Compiler.xls" Then End
Application.DisplayAlerts = True
Windows("SK Compiler.xls").Activate
Sheets("Main").Select
Range("A1").Select
Loop
End Sub
Posted by Ivan Moala on April 20, 2000 1:12 PM
ActiveWindow.DisplayWorkbookTabs = True ActiveWindow.ActivateNext Application.DisplayAlerts = False ActiveWindow.Close If ActiveWorkbook.Name = "SK Compiler.xls" Then End Application.DisplayAlerts = True
Hi Del
Without really testing it.
Try taking out the,If ActiveWorkbook.Name = "SK Compiler.xls" Then End
Ivan
Posted by Del on April 26, 2000 1:24 AM
ActiveWindow.DisplayWorkbookTabs = True ActiveWindow.ActivateNext Application.DisplayAlerts = False ActiveWindow.Close If ActiveWorkbook.Name = "SK Compiler.xls" Then End Application.DisplayAlerts = True
Ivan,
Unfortunatly it closes down the workbook that is required to stay open (SK Compiler).
Is there anything else we can do.
Del.
Posted by Ivan Moala on May 03, 2000 7:17 AM
Del
Try this instead.Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do Until Empty
ActiveWindow.ActivateNext
ActiveSheet.Select
ActiveSheet.Move After:=Workbooks("SK Compiler.xls").Sheets(2)
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.ActivateNext
ActiveWindow.Close
If ActiveWorkbook.Name = "SK Compiler.xls" Then Exit Do
Loop
Windows("SK Compiler.xls").Activate
Sheets("Main").Select
Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Ivan
Posted by Ivan Moala on May 04, 2000 4:36 AM
I think this one will do what you want
the other way gives errors when the activeworkbook
has only 1 sheet and you are moving the sheet.
Better to copy it over.
Sub AnotherWay()
Dim WBk
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each WBk In Application.Workbooks
If WBk.Name <> "SK Compiler.xls" Then
WBk.Activate
ActiveSheet.Copy After:=Workbooks("SK Compiler.xls").Sheets(2)
WBk.Activate
WBk.Close
End If
Next
Windows("SK Compiler.xls").Activate
Sheets("Main").Select
Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
