Macro to move sheet from one document to another?

[tonywatsonhelp]

Hi Everyone,
I need to be able to move a tab from the current open document to another document call "Master1"

the other document is located at this directory route "F:\MY Docs1\New1\134 Upper\Master1.xlsm"

the sheet i want to move is just "active sheet" and "this workbook".

I cant get it to work any ideas?

Tony

 

[louisH]

Hi,

If the other workbook is opened :

'To copy
ActiveSheet.Copy After:=Workbooks("Master1.xlsm").Sheets(1)
'To move
ActiveSheet.Move After:=Workbooks("Master1.xlsm").Sheets(1)

if not it's a little bit more complicated

 

[louisH]

There you go if the file is not opened :

Sub MoveSheet()
    
    Dim xl As Application
    Dim wb As Workbook
    Dim Path As String
    
    Path = "F:\MY Docs1\New1\134 Upper\Master1.xlsm"
    
    If IsFileOpen(Path) Then
        MsgBox "File already opened"
        Exit Sub
    End If
    
    Set xl = CreateObject("Excel.Application")
    xl.Visible = False
    Set wb = xl.Workbooks.Open(Path)
    
    ThisWorkbook.ActiveSheet.Move After:=wb.Sheets(wb.Sheets.Count)
    'Or ThisWorkbook.ActiveSheet.Copy After:=wb.Sheets(wb.Sheets.Count)
    
    wb.Close True
    xl.Quit
    Set xl = Nothing
    
End Sub

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next
    filenum = FreeFile()
    Open filename For Input Lock Read As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenum]#filenum[/URL] 
    Close filenum
    errnum = Err
    On Error GoTo 0
    Select Case errnum
        Case 0
         IsFileOpen = False
        Case 70
            IsFileOpen = True
        Case Else
            Error errnum
            
    End Select
End Function

+ an IsFileOpen function to check if somebody is using the Master1.xlsm file.

 

[tonywatsonhelp]

Thank you LouisH,
this is just excellent
the second one is what i need

Tony