I've created a new worksheet within a blank workbook, and this has the new
date range for charts for 2003-2005 and have 300+ client worksheets that
these blank worksheet needs to be in, is there a way of copying and pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do 300+ would
be extremely time consuming. Any suggestions will be appreciated.

Give us some more detail regarding your data and we can probably help more
than you think. The sheet you have created and the 12 charts on it, do they
have any links to other sheets, or is the sheet a self contained thing.

If you do have links to other sheets in the file then are the links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this has the new
date range for charts for 2003-2005 and have 300+ client worksheets that
these blank worksheet needs to be in, is there a way of copying and
pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do 300+
would
be extremely time consuming. Any suggestions will be appreciated.

Ken,

Thanks for your response. The 2003 - 2005 worksheet I have pulls information
from 3 different datasheets within the same workbook and the time it will
take to go into each graph in the 2003 -2005 worksheet and change the data
series for all 12 graphs on that specific worksheet will take for ever. I
look forward to any suggestions you may have.

"Ken Wright" wrote:

Give us some more detail regarding your data and we can probably help more
than you think. The sheet you have created and the 12 charts on it, do they
have any links to other sheets, or is the sheet a self contained thing.

If you do have links to other sheets in the file then are the links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this has the new
date range for charts for 2003-2005 and have 300+ client worksheets that
these blank worksheet needs to be in, is there a way of copying and
pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do 300+
would
be extremely time consuming. Any suggestions will be appreciated.

Ok, so is every link going to be identical, eg a link that points to
sheet1!A25 in one workbook will point to that range in every workbook.

Also what version Excel are you using?

Where in the workbooks is this sheet going to go, eg first sheet, last sheet
or where?

Is there already a sheet in the workbook that needs to be deleted, and if so
what is it's name and then are there any other sheets in that workbook that
currently link to the worksheet to be deleted?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Yes, the links are identical in every workbook. The worksheet will pull from
the data sheet 2003, data sheet 2004 and 2005. I tried copying the sheet to
one of the clients workbooks last night, but it linked back to the original
workbook.

"Deb" wrote:

Ken,

Thanks for your response. The 2003 - 2005 worksheet I have pulls information
from 3 different datasheets within the same workbook and the time it will
take to go into each graph in the 2003 -2005 worksheet and change the data
series for all 12 graphs on that specific worksheet will take for ever. I
look forward to any suggestions you may have.

"Ken Wright" wrote:

Give us some more detail regarding your data and we can probably help more
than you think. The sheet you have created and the 12 charts on it, do they
have any links to other sheets, or is the sheet a self contained thing.

If you do have links to other sheets in the file then are the links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this has the new
date range for charts for 2003-2005 and have 300+ client worksheets that
these blank worksheet needs to be in, is there a way of copying and
pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do 300+
would
be extremely time consuming. Any suggestions will be appreciated.

OK, I need the answers to my questions about any formulas on any sheets
linking to your chart sheet, whether a sheet exists to be deleted, if so
what is the name, where you want the sheet to go and what version excel.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
Yes, the links are identical in every workbook. The worksheet will pull
from
the data sheet 2003, data sheet 2004 and 2005. I tried copying the sheet
to
one of the clients workbooks last night, but it linked back to the
original
workbook.

"Deb" wrote:

Ken,

Thanks for your response. The 2003 - 2005 worksheet I have pulls
information
from 3 different datasheets within the same workbook and the time it
will
take to go into each graph in the 2003 -2005 worksheet and change the
data
series for all 12 graphs on that specific worksheet will take for ever.
I
look forward to any suggestions you may have.

"Ken Wright" wrote:

Give us some more detail regarding your data and we can probably help
more
than you think. The sheet you have created and the 12 charts on it,
do they
have any links to other sheets, or is the sheet a self contained
thing.

If you do have links to other sheets in the file then are the links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission
:-)

--------------------------------------------------------------------------
--

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this has
the new
date range for charts for 2003-2005 and have 300+ client worksheets
that
these blank worksheet needs to be in, is there a way of copying and
pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do
300+
would
be extremely time consuming. Any suggestions will be appreciated.

I have Office XP and the new 2003-2005 will be the second to last tab on the
right, last one is labeled blank and no sheets will be deleted. Thanks for
your help.


"Ken Wright" wrote:

OK, I need the answers to my questions about any formulas on any sheets
linking to your chart sheet, whether a sheet exists to be deleted, if so
what is the name, where you want the sheet to go and what version excel.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
Yes, the links are identical in every workbook. The worksheet will pull
from
the data sheet 2003, data sheet 2004 and 2005. I tried copying the sheet
to
one of the clients workbooks last night, but it linked back to the
original
workbook.

"Deb" wrote:

Ken,

Thanks for your response. The 2003 - 2005 worksheet I have pulls
information
from 3 different datasheets within the same workbook and the time it
will
take to go into each graph in the 2003 -2005 worksheet and change the
data
series for all 12 graphs on that specific worksheet will take for ever.
I
look forward to any suggestions you may have.

"Ken Wright" wrote:

Give us some more detail regarding your data and we can probably help
more
than you think. The sheet you have created and the 12 charts on it,
do they
have any links to other sheets, or is the sheet a self contained
thing.

If you do have links to other sheets in the file then are the links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission
:-)

--------------------------------------------------------------------------
--

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this has
the new
date range for charts for 2003-2005 and have 300+ client worksheets
that
these blank worksheet needs to be in, is there a way of copying and
pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do
300+
would
be extremely time consuming. Any suggestions will be appreciated.

I have a routine that will allow you to select a top level directory, open
up every excel file within it, copy a specific sheet from an already open
file across to the files, change all the links in the files to point back to
the files themselves (thereby removing the links). just don't want you to
hit any problems that I could stave off by knowing the answers to those
questions. Give me those and I'll make some final tweaks and post what you
need (hopefully).

Just in case I forget though, Try anything I post on a COPY of the directory
with all your files first. Always always back up your data first. :-)

Have to hit the sack pretty soon, so may have to pick this up tomorrow
(Nearly 01:00 here now - UK)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
Yes, the links are identical in every workbook. The worksheet will pull
from
the data sheet 2003, data sheet 2004 and 2005. I tried copying the sheet
to
one of the clients workbooks last night, but it linked back to the
original
workbook.

"Deb" wrote:

Ken,

Thanks for your response. The 2003 - 2005 worksheet I have pulls
information
from 3 different datasheets within the same workbook and the time it
will
take to go into each graph in the 2003 -2005 worksheet and change the
data
series for all 12 graphs on that specific worksheet will take for ever.
I
look forward to any suggestions you may have.

"Ken Wright" wrote:

Give us some more detail regarding your data and we can probably help
more
than you think. The sheet you have created and the 12 charts on it,
do they
have any links to other sheets, or is the sheet a self contained
thing.

If you do have links to other sheets in the file then are the links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission
:-)

--------------------------------------------------------------------------
--

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this has
the new
date range for charts for 2003-2005 and have 300+ client worksheets
that
these blank worksheet needs to be in, is there a way of copying and
pasting
into other workbooks, without having to redo the data series in each
workbook. This worksheet has 12 individual charts on it, so to do
300+
would
be extremely time consuming. Any suggestions will be appreciated.

OK, assumptions as follows:-

You have a Master workbook that matches exactly in structure (With the
exception of the chart sheet) every one of your 300 files.

Except for the chart sheet, the names of all the sheets in your Master
workbook are identical to those of the ones in your 300 files (Should have
been covered by first caveat, but better safe than sorry).

Your worksheet with the charts on it is called 2003 - 2005 (Note single
spaces around hyphen). Anythimg different to this you MUST look for the
string 2003 - 2005 in the code and change it to EXACTLY what your sheet is
called.

Your Master workbook does NOT have the same name as any one of those files.

Your Chart Sheet is not named the same as any of the existing sheets in any
file.

You have NO other Excel files in your directory structure. If you have then
move them out and then back again after.

You WILL try this first on either a small sample directory of copies, OR
make sure you back up your data first!!! Must must must do this!!! :-)



Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(2003 - 2005)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to what you see at the moment in my note (eg the 2003 -
2005 bit)

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in to the white space that
appears, all the following code from between the marker lines (Not the lines
though)

Code Below (Don't copy this line - just the stuff below the marker lines)
===========================================

Option Explicit
Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, F As Object
Set SA = CreateObject("Shell.application")
Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not F Is Nothing) Then
PickFolder = F.items.Item.path
End If
Set F = Nothing
Set SA = Nothing
End Function

Sub CopyCharts()

Dim ffc As Long
Dim i As Long
Dim sc As Long
Dim TgtWkb As Workbook
Dim TgtWkbn As String
Dim TgtWks As Worksheet
Dim CurWkb As Workbook
Dim CurWkbn As String
Dim CurWks As Worksheet
Dim CurWksLrow As Long
Dim strStartDir As String
Dim UserFile As String

' On Error Resume Next
UserFile = PickFolder(strStartDir)
If UserFile = "" Then
MsgBox "Canceled"
End If

'CurWks will always refer to the Chart worksheet to be copied over
Set CurWkb = ActiveWorkbook
CurWkbn = CurWkb.Name
'This MUST be exactly what your sheet is called
Set CurWks = CurWkb.Worksheets("2003 - 2005")

Application.ScreenUpdating = False

With Application.FileSearch
.SearchSubFolders = True
.NewSearch
.Filename = ".xls"
.LookIn = UserFile
.FileType = msoFileTypeExcelWorkbooks
.Execute

ffc = .FoundFiles.Count

For i = 1 To ffc

'TgtWkb will always refer to the workbook you are copying the charts
to.
Set TgtWkb = Application.Workbooks.Open(Filename:=.FoundFiles(i ))
Application.StatusBar = "Currently Processing file " & i & " of " &
ffc

With TgtWkb
TgtWkbn = .Name
sc = .Sheets.Count - 1
CurWks.Copy After:=Workbooks(TgtWkbn).Sheets(sc)
.ChangeLink Name:=CurWkbn, NewName:=TgtWkbn, Type:=xlExcelLinks
.Save
.Close
End With

Next i
End With

Set TgtWkb = Nothing
Set TgtWks = Nothing
Set CurWkb = Nothing
Set CurWks = Nothing

Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

===========================================
Code Above (Don't copy this line - just the stuff above the marker lines)

Then hit File / Close and return to Microsoft Excel and save the file. Now
just do Tools / Macro / Macros / CopyCharts, choose the top level directory
and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip

Damn wordwrap

the following lines should instead of being as they appear

'TgtWkb will always refer to the workbook you are copying the charts
to.
Set TgtWkb = Application.Workbooks.Open(Filename:=.FoundFiles(i ))
Application.StatusBar = "Currently Processing file " & i & " of " &
ffc

actually be

'TgtWkb will always refer to the workbook you are copying the charts to.
Set TgtWkb = Application.Workbooks.Open(Filename:=.FoundFiles(i ))
Application.StatusBar = "Currently Processing file " & i & " of " & ffc

Wordwrap has dropped the last words on to the next line. Need to fix if it
appears that way in the code.

Going to sleep now, but let me know how you get on and I'll catch up
tomorrow :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Deb, if you want to send me a single file with your charts in - dummy out
any sensitive data, then I can send it back to you with what you need to run
this. You would need to take the NOSPAM out of my email address.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Deb" wrote in message
...
I have Office XP and the new 2003-2005 will be the second to last tab on
the
right, last one is labeled blank and no sheets will be deleted. Thanks
for
your help.


"Ken Wright" wrote:

OK, I need the answers to my questions about any formulas on any sheets
linking to your chart sheet, whether a sheet exists to be deleted, if so
what is the name, where you want the sheet to go and what version excel.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"Deb" wrote in message
...
Yes, the links are identical in every workbook. The worksheet will
pull
from
the data sheet 2003, data sheet 2004 and 2005. I tried copying the
sheet
to
one of the clients workbooks last night, but it linked back to the
original
workbook.

"Deb" wrote:

Ken,

Thanks for your response. The 2003 - 2005 worksheet I have pulls
information
from 3 different datasheets within the same workbook and the time it
will
take to go into each graph in the 2003 -2005 worksheet and change
the
data
series for all 12 graphs on that specific worksheet will take for
ever.
I
look forward to any suggestions you may have.

"Ken Wright" wrote:

Give us some more detail regarding your data and we can probably
help
more
than you think. The sheet you have created and the 12 charts on
it,
do they
have any links to other sheets, or is the sheet a self contained
thing.

If you do have links to other sheets in the file then are the
links
identical in all the other workbooks?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask
permission
:-)


--------------------------------------------------------------------------
--

"Deb" wrote in message
...
I've created a new worksheet within a blank workbook, and this
has
the new
date range for charts for 2003-2005 and have 300+ client
worksheets
that
these blank worksheet needs to be in, is there a way of copying
and
pasting
into other workbooks, without having to redo the data series in
each
workbook. This worksheet has 12 individual charts on it, so to
do
300+
would
be extremely time consuming. Any suggestions will be
appreciated.