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!

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

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

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

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

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

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

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

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