Activate next

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
I have an application that uses the following code:

Application.ActiveWindow.ActivateNext
Range("A1:Z500").Select
Selection.Copy
Application.ActiveWindow.ActivatePrevious
ActiveSheet.Paste

When there is no other workbook open, the current workbook copies from itself.

How can I make the code show an error and end the procedure when there is no other open workbook?

Thanks
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
304
Use the Application.Workbooks.Count property as in:
Code:
If Application.Workbooks.Count = 2 Then
    Application.ActiveWindow.ActivateNext 
    Range("A1:Z500").Copy 
    Application.ActiveWindow.ActivatePrevious 
    ActiveSheet.Paste 
ElseIf Application.Workbooks.Count = 1 Then
    MsgBox "...you only have 1 workbook open!",vbCritical, "Operation cancelled..."
    Exit Sub
Else
    MsgBox "...you have at least 2 workbooks open!  Your use of .ActivateNext and ActivatePrevious could be potentially catastrophic!",vbCritical, "Operation cancelled..."
    Exit Sub
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,119,123
Messages
5,576,227
Members
412,709
Latest member
AD04
Top