Import Worksheets and "Overright"

A

Apprentice

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 before:= _
basebook.Sheets(basebook.Sheets.Count)

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

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 before:= _
basebook.Sheets(basebook.Sheets.Count)
mybook.Close False
Next
Click to expand...

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").pastespecial paste:=xlpasteformulas
basebook.worksheets(wks.name).range("a1").pastespecial paste:=xlpasteformats
next wks
mybook.close savechanges:=false
next n

(Untested, uncompiled. Watch for typos.)
 
A

Apprentice

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").PasteSpecial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpecial Paste:=xlPasteFormats

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

Dave Peterson

Why did you drop the "For each wks in mybook.worksheets" and the corresponding
"next wks" lines?
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").PasteSpecial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpecial Paste:=xlPasteFormats

'Next wks
mybook.Close savechanges:=False
Next N
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
Click to expand...
 
A

Apprentice

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").PasteSpecial Paste:=xlPasteFormulas
Click to expand...
basebook.Worksheets(wks.Name).Range("a1").PasteSpecial 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").PasteSpecial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpecial 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
 
D

Dave Peterson

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???



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").PasteSpecial Paste:=xlPasteFormulas
Click to expand...
basebook.Worksheets(wks.Name).Range("a1").PasteSpecial 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").PasteSpecial Paste:=xlPasteFormulas
basebook.Worksheets(wks.Name).Range("a1").PasteSpecial 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
Click to expand...
 
A

Apprentice

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
 
A

Apprentice

Clearification:

the Basebook has 8 worksheets, 7 are the same as the origin workbook. In
both workbooks 3 of the wks are hidden.

The basebook has 1 worksheet that the origin does not have [Charts]. The
seven being copied into the basebook will update the 8th in basbook.

Hope this helps my concept of operation.
 
D

Dave Peterson

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.
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 said:
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???
Click to expand...
Click to expand...
 
A

Apprentice

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 said:
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.
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copying worksheets 3
copy worksheets 6
Copying Multiple WorkSheets into 1 Workbook - Ron de Bruin Help 3
Code not working NOW ? But it was before??? 2
help to modify code to look to correct folder 5
HELP TO COMBINE MULTIPLE SHEETS IN MULTIPLE WORKBOOKS 12
opening order macro for files in a folder 1
CSE formulas in vba(ie: averageif in Excel 2003) 1

Top