Dave that worked great and all is working now. I've been out for a week on
travel, sorry I didn;t respond earlier.

Thanks
--
Your guidance is greatly appreciated!


"Dave Peterson" wrote:

If you always know the names of sheets to avoid:

for each wks in mybook.worksheets
select case lcase(wks.name)
case "charts","sheet9999","anothername"
'skip it
case else
'do the copy paste here
end select
next wks

Fix those names (sheet9999 and anothername) or delete them if you don't need
them.

Apprentice wrote:

The worksheet in the destination workbook, is the Chart Worksheet. And you
are right, the origin workbook does not have this worksheet.

How can I code to import all except the [Charts] worksheet.

Also does hidden worksheets have any other effect on either origin or
destination workbooks.

thanks
--
Your guidance is greatly appreciated!

"Dave Peterson" wrote:

That means that the basebook that that use is trying to modify doesn't have a
worksheet with that name (that you're copying from).

Add a line like:

for each wks in basebook.worksheets
msgbox wks.name
...

And you'll see the offending name.

The next question is what should happen next? Should that worksheet be ignored
or should a new worksheet be created with that name???




Apprentice wrote:

Don't know why.... my bad. It works great now thankyou.

I forworded the workbook with code to a counterpart. We adjusted the path
to meet his file structure. On his PC the code works great until this line:

wks.Cells.Copy
basebook.Worksheets(wks.Name).Range("a1").PasteSpe cial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpe cial Paste:=xlPasteFormats

We both have the same versions of Excel and all other things are equal, but
the error says "out of range".

What would cause the code to not work on anothers PC?

Any ideas?

Here is the whole code:

Sub Import()

Dim basebook As Workbook
Dim mybook As Workbook
Dim wks As Worksheet
Dim N As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\bekuta\My Documents\Kuta\work\tech
assistance\hrmc\Employee Survey\ES2009\OrgWorkbooks\"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ActiveWorkbook
'New

For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
For Each wks In mybook.Worksheets
wks.Cells.Copy
basebook.Worksheets(wks.Name).Range("a1").PasteSpe cial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpe cial Paste:=xlPasteFormats


Next wks
mybook.Close savechanges:=False
Next N
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Thanks for your time Dave

--
Your guidance is greatly appreciated!

"Dave Peterson" wrote:

Why did you drop the "For each wks in mybook.worksheets" and the corresponding
"next wks" lines?

Apprentice wrote:

Thanks Dave but I need just a little more help. I incorporated your code and
here is what I have. Its hanging on the

Wks.Cell.Copy lines...... Also notice I commented out the Next wks.... that
also was hanging

Sub Import()

Dim basebook As Workbook
Dim mybook As Workbook
Dim wks As Worksheet
Dim N As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\eberger\My Documents\Employee
Survey\ES2009\NewWorkbooks\"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ActiveWorkbook
'New
'For each wks in mybook.worksheets
For N = LBound(FName) To UBound(FName)

Set mybook = Workbooks.Open(FName(N))
wks.Cells.Copy
basebook.Worksheets(wks.Name).Range("a1").PasteSpe cial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpe cial Paste:=xlPasteFormats

'Next wks
mybook.Close savechanges:=False
Next N
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
--
Your guidance is greatly appreciated!

"Dave Peterson" wrote:

Instead of copying the worksheets, just copy the values and formulas (and
formats???).

You'll have to make sure that each worksheet exists in the receiving workbook,
but that seems reasonable if you already have charts that refer to data on those
sheets.


For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
mybook.Worksheets.Copy befo= _
basebook.Sheets(basebook.Sheets.Count)
mybook.Close False
Next

dim wks as worksheet
....
for n = lbound(fname) to ubound(fname)
set mybook = workbooks.open(fname(n))
For each wks in mybook.worksheets
wks.cells.copy _
destination:=basebook.worksheets(wks.name).range(" a1")
'or
wks.cells.copy
basebook.worksheets(wks.name).range("a1").pastespe cial paste:=xlpasteformulas
basebook.worksheets(wks.name).range("a1").pastespe cial paste:=xlpasteformats
next wks
mybook.close savechanges:=false
next n

(Untested, uncompiled. Watch for typos.)


Apprentice wrote:

I have this great code that imports worksheets into the active workbook. I
want the imported worksheets to overright the existing worksheets with the
same name. The imported and resident worksheets are all of the same name. I
don't want the imported worksheet to take the name of Worksheet (2.

The reason I'm doing this is because of charts that use the existing
referenced worksheets.

here is the code I have so far, all works fine except the (2):

Sub Import()
Dim basebook As Workbook
Dim mybook As Workbook
Dim N As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\eberger\My Documents\Employee
Survey\ES2009\NewWorkbooks\"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ActiveWorkbook

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
mybook.Worksheets.Copy befo= _
basebook.Sheets(basebook.Sheets.Count)

mybook.Close False
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

--
Your guidance is greatly appreciated!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson