import sheets


i need to import sheets of following names from "MYProject.xls" in m
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

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=37667

Is my question is cleared?

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=37667

Assume MyProject.xls is open

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,"#" & sh.Name & "#",s,vbTextCompare)
if iloc < 0 then
v(ubound(v)) = sh.Name
s = Replace(s,sh.Name & "#","")
redim Preserve v(0 to ubound(v) + 1)
end if
Next
if len(s) < 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

--
Regards,
Tom Ogilvy

"ilyaskazi" wrote
in message ...

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

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

Tom,

I was not able to get any output from your code, bcoz it gives me debu
messages. Whereas..


Patrick,
Your code was successful to import sheets only if array containin
names matches with workbook sheet names.

But what about if array containing names does not found any i
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

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=37667

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" wrote
in message ...

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