How to Copy Worksheet from Another Workbook, Rename it and 

check whether Other Workbook was Already Open

Home More Excel (VBA) Training Videos

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:

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"

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

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 video on YouTube

Home More Excel (VBA) Training Videos