import sheets of name selected

Option Explicit

Public Sub GetSheets()

Dim wbP As Workbook
Dim ws As Worksheet
Dim MySheets() As Variant
Dim index As Long
MySheets = Array("RULES", "CONDITIONS", "ROUTING", "ZONES")
Set wbP = Workbooks.Open("C:\MyProject.xls")

For index = LBound(MySheets, 1) To UBound(MySheets, 1)

Set ws = wbP.Worksheets(MySheets(index))

ws.Copy ThisWorkbook.Sheets(1)


Next


wbP.Close False



"ilyaskazi" wrote:

>
> import sheets
>
>
> i need to import sheets of following names from "MYProject.xls" in my
> new workbook.
>
> RULES, CONDITIONS, ROUTING, ZONES.
>
>
> Only check above sheet names in MyProject.xls and import in new wrkbk.
> If not found any then display its name in msgbox.
>
>
> --
> ilyaskazi
> ------------------------------------------------------------------------
> ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
> View this thread: http://www.excelforum.com/showthread...hreadid=376676
>
>

Here is a slight revision that worked perfectly for me, but if you want to
wait for Patrick, knock yourself out.

Sub abc()
Dim v As Variant
Dim s As String
Dim s1 As String
Dim iloc As Long
s = "#RULES#CONDITIONS#ROUTING#ZONES#"
s1 = s
ReDim v(0 To 0)
For Each sh In Workbooks("Myproject.xls").Worksheets
iloc = InStr(1, s, "#" & sh.Name & "#", vbTextCompare)
If iloc <> 0 Then
v(UBound(v)) = sh.Name
s = Replace(s, UCase(sh.Name) & "#", "")
ReDim Preserve v(0 To UBound(v) + 1)
End If
Next
If Len(s) < Len(s1) Then
ReDim Preserve v(0 To UBound(v) - 1)
Workbooks("Myproject.xls").Worksheets(v).Copy
End If
If Len(s) > 1 Then
s = Right(s, Len(s) - 1)
s = Replace(s, "#", vbCr)
MsgBox "Sheets not found: " & vbCr & s
End If

End Sub

--
Regards,
Tom Ogilvy

"ilyaskazi" <ilyaskazi.1q99ah_1118146090.2002@excelforum-nospam.com> wrote
in message news:ilyaskazi.1q99ah_1118146090.2002@excelforum-nospam.com...
>
> Tom,
>
> I was not able to get any output from your code, bcoz it gives me debug
> messages. Whereas..
>
>
> Patrick,
> Your code was successful to import sheets only if array containing
> names matches with workbook sheet names.
>
> But what about if array containing names does not found any in
> workbook???
> Suppose workbook does not contain sheets which are required to import,
> then display msgbox with its sheet-name not found.
>
> Plz check the same and let me know.
>
>
> --
> ilyaskazi
> ------------------------------------------------------------------------
> ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
> View this thread: http://www.excelforum.com/showthread...hreadid=376676
>