Copying a worksheet between workbooks using cells as guidance on what to copy-CodePudding

I have the following in Call.xlsm, A2 contains the path to a second Workbook, Data.xlsm. A3 holds the sheetname I'm trying to copy from Data.xlsm to Call.xlsm.

I understand the first step to copying a sheet from another workbook, is to open it the other workbook (this is in Call.xlsm):

Sub GetData()
    Dim filenameIS As String
    filenameIS = Worksheets("Sheet1").Range("a2")
    Workbooks.Open (filenameIS)
    
    Workbooks(filenameis).WorkSheets("Data 2018").CopyBefore:=ThisWorkbook.Sheets(1))

End Sub

This returns:

Compile error: Synatax error

It doesn't like the :=

CodePudding user response：

Try this:

Sub GetData()
    Dim filenameIS As String, wb As Workbook, wsInfo As Worksheet
    
    Set wsInfo = ThisWorkbook.Worksheets("Sheet1")
    filenameIS = wsInfo.Range("a2")
    
    Set wb = Workbooks.Open(filenameIS) 'get a reference to the opened workbook
    'Copy the worksheet named in A3 over to `wb`
    wb.Worksheets(wsInfo.Range("A3").Value).Copy _
         Before:=ThisWorkbook.Worksheets(1) 

End Sub

CodePudding user response：

Import Sheet From Closed Workbook

Sub ImportSheet()
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Sheets("Sheet1")
    
    Dim sFilePath As String: sFilePath = CStr(dws.Range("A2").Value)
    Dim sSheetName As String: sSheetName = CStr(dws.Range("A3").Value)
    
    Dim IsFound As Boolean
    IsFound = CreateObject("Scripting.FileSystemObject").FileExists(sFilePath)
    
    If Not IsFound Then
        MsgBox "The file '" & sFilePath & "' doesn't exist.", vbExclamation
        Exit Sub
    End If
    
    Dim swb As Workbook: Set swb = Workbooks.Open(sFilePath)
     
    Dim sws As Object ' if it's a worksheet, use 'Dim sws As Worksheet'
    On Error Resume Next
        Set sws = swb.Sheets(sSheetName)
    On Error GoTo 0
    
    If Not sws Is Nothing Then sws.Copy Before:=dwb.Sheets(1)
    
    swb.Close SaveChanges:=False
    
    If sws Is Nothing Then
        MsgBox "Sheet '" & sSheetName & "' doesn't exist.", vbExclamation
    Else
        MsgBox "Sheet '" & sSheetName & "' imported.", vbInformation
    End If
    
End Sub