March 18, 2014

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”

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 training video:

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

4 thoughts on “Copy Worksheet from Another Workbook Rename

    1. Sir this is Ali here from Portugal.. Please Sir i want to contact you if you have any whatsapp number please contact me by email.


  1. Sir this is Ali here from Portugal.. Please Sir i download file but when i open and log in with zuri and azwan its show compile error:

    ” Cant find project or library”


Comments are closed.