Create Move Macro for Closed Workbook

[Roger]

Hello,

I have a Worksheet in a Book that I’m moving to another Workbook (“WKB2â€).
I’m looking to create a Macro which looks to see if “WKB2†is closed. If
it’s closed, I would like to insert the sheet from the first Workbook
(“WKB1â€).

Since I only want to have it perform the action if the other workbook is
closed, is there also a way to create a message to alert the user in the
event the workbook is open - - - meaning don't move it in that case?

Thanks for your review – Roger

 

[Jeff]

This is a function a friend helped me to create when I had a similar issue:
you will need to insert "Part 1" in your macro and use "Part 2" to create a
"public function" (similar to a new macro) in your module.

"Part 1" - put the following in your macro:

If WorkbookIsOpen("WORKBOOK NAME") <> True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

"Part 2" - just copy and past this code in your module after your macro
(after the "End Sub"

Public Function WorkbookIsOpen(wbname) As Boolean

' Returns TRUE if the workbook is open

Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then
WorkbookIsOpen = True
Else
WorkbookIsOpen = False
End If

End Function

What this will do is give "TRUE" if the workbook you name (in the IF
statement above) is open. Of course you can use the "IF...ELSE...END IF" to
give you a warning message should the workbook be open.

 

[Jeff]

Just so there is no confusion, your IF... Then statement should look as
follows:

If WorkbookIsOpen("WKB2") <> True Then
'Insert your code here to move the sheet
Else
'Insert a warning message or something to tell you the workbook was open.
End If

Hope this helps.