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