VBA to import a tab from another workbook

[gheyman]

Is there a way VBA to have the user identify a workbook and then once they selected the workbook to copy in a tab from that workbook?

Only if the workbook has a tab named "CMCS Bill of Materials".
But I also want to make sure the current workbook doesn't already have a tab by that name. (if it does, something like a MsgBox that says they must first delete the current tab named "CMCS Bill of Materials" and or give them an option to delete it before proceeding).

Hope this isn't too much.

Thanks for the Help. Always trying to learn.

 

[gheyman]

I got most of the way there. But when I run this, it gives me the MsgBox message even though the workbook that is opening does contain a tab with the name "CMCS Bill of Materials"

If I take out the part thats checking for the tab, the code woks.

Any help figuring out why it thinks the opened workbook (CMCS) doesn't have that tab when it does is very much appreciated

Sub GetCMCS()

    Dim ProposalReports As Workbook
    Dim CMCS As Variant


    Set ProposalReports = ThisWorkbook
    CMCS = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    
    If CMCS <> False Then
        Workbooks.Open (CMCS)
        Set CMCS = ActiveWorkbook
        
         If Not Evaluate("isref(CMCS Bill of Materials!a2)") Then
            MsgBox "The Selected Workbook does not contain a CMCS Bill of Materials tab, check to validate the tab is named correctly or select a different Workbook"
            Exit Sub
        Else
        
      Sheets("CMCS Bill of Materials").Select
            Sheets("CMCS Bill of Materials").Copy Before:=ThisWorkbook.Sheets(2)
    
    
    
    End If
    End If


End Sub

thank you!

 

[NdNoviceHlp]

Hi gheyman. Maybe something like this. HTH. Dave

Dim sht As Worksheet, Flag As Boolean
Workbooks.Open (CMCS)
For Each sht In Workbooks("CMCS").Worksheets
If LCase(sht.Name) = LCase("CMCS Bill of Materials") Then
sht.copy Before:=ThisWorkbook.Sheets(2)
Flag = True
Exit For
End If
Next sht
If Not Flag Then
MsgBox "The Selected Workbook does not contain a CMCS Bill of Materials tab!" & vbCrLf _
& "Check to validate the tab is named correctly or select a different Workbook"
End If