Copy Worksheet from Another Workbook Rename

How to Copy Worksheet from Another Workbook, Rename it and check whether Other Workbook was Already Open

Many YouTube viewers of my channel wanted to know how to copy a worksheet from another workbook and rename it automatically. They also wanted to know how to check whether the other workbook was already open or not. This can be quite important in a networked environment where many people wish to copy data from another workbook. In case the file is not opened by another user, we can open it, do the necessary actions while locking it so that we can complete the work.
The complete macro code and explanations are given below. An apostrophe indicates a remark.

Sub copysheetfromanotherworkbook()
Dim info
‘we check whether the workbook is open using a function that is shown below
info = IsWorkBookOpen(“C:\takyar\Desktop\def.xlsm”)
If info = True Then
MsgBox “File is being used”

Else
MsgBox “file is closed!”
End If
‘ we open the workbook if it is closed
If info = False Then
Workbooks.Open FileName:=”C:\Users\takyar\Desktop\def.xlsm”
End If
‘ we copy the worksheet d into abc.xlsm after the sheet c
Sheets(“d”).Copy after:=Workbooks(“abc.xlsm”).Sheets(“c”)
‘we use the inputbox to rename the copied sheet
Sheets(Sheets.Count).Name = InputBox(“Assign a new name”)
‘finally we close the opened workbook
Workbooks(“def.xlsm”).Close

End Sub

‘ This function checks to see if a workbook is open or not. If the workbook is
‘ open, it returns True. If the workbook is not open, it returns
‘ false. Else, a run-time error occurs since there is
‘ some other problem accessing the workbook and we capture that error number for further action.

Function IsWorkBookOpen(FileName As String)

Dim FF As Integer, ErrNum As Integer

On Error Resume Next ‘ We turn off error checking
FF = FreeFile() ‘ The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF ‘we try to open the file and lock it
Close FF ‘ Close the file
ErrNum = Error ‘ capture the error number
On Error GoTo 0 ‘ Turn on error checking
‘Find which error happened
Select Case ErrNum
‘ No error
‘ File is not open
Case 0: IsWorkBookOpen = False
‘ Error for “Permission Denied.”
‘ File already opened by another user
Case 70: IsWorkBookOpen = True
‘ Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
End Select
End Function

Watch the training video:


Further reading:
Macro Code to Check Whether a File Is Already Open

Leave a Reply

Your email address will not be published. Required fields are marked *