MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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