Copying existing sheets to another Workbook

  • Thread starter Thread starter Smonczka
  • Start date Start date
S

Smonczka

I need help copying an existing sheet along with the links on that
sheet to another workbook. I have two workbooks, say Workbook1 and
Workbook2. They are the same exact workbooks with the same worksheets
in them, the same layout. Only the data is different. In workbook 1 I
have inserted a sheet that summarizes the data from the other sheets in
that workbook. Now I want to copy that sheet to Workbook2 so that I
don't have to make a summary page for Workbook2.

The problem is that the sheet has links in it and when I copy and/or
move the sheet to the new workbook those links are still linked to the
old workbook.

Is there a function or command that I am supposed to use when doing
this so that the links are dynamic to the workbook it is being copied
to?

Thanks for the help.
Steve Monczka
(e-mail address removed)
 
Have both WBs open.
Right click in the tab of the "Summary" sheet and choose "Move or Copy".
Expand the "To Book" box, and click on the name of the new WB.
Click in "Create A Copy", then <OK>.

You're now in the new WB.
<Edit> <Links>
In the "Source File" window, you'll see the name of the old WB.
Click the "Change Source" button.
You'll now see a listing of all the WBs in the folder.
Simply click on the name of the new WB, then <OK>.
Your Source File window should now be empty, since there are now *no*
external links, since all links are now self-contained.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
RD,

Thanks for the help and that works perfectly, but would you know of a
quicker way to do it? I have 83 workbooks I have to do this for and I
want to try to get home by XMass.

Thanks again for your sugestion.
Steve
 
Can you put those 83 workbooks, er, copy those 83 workbooks to their own
dedicated folder?

If you can then maybe a macro could go through and do all the work.

I like to just change my formulas to text (mass change = to $$$$$=). Then copy
the sheet. Since the cells contain text--there are no links back to the old
workbook. Then just one more mass change ($$$$$= back to =) and the formulas
come back to life.

But please make sure that the worksheets with the correct names are in the
receiving workbook. Else you'll have lots of dialogs to dismiss.

And if you copy those 83 workbooks to that new folder, if this doesn't work for
you, you can just remove that folder!

Option Explicit
Sub testme01()

Dim MstrSummaryWks As Worksheet

Dim tempWkbk As Workbook

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "C:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
'Change to match the summary worksheet name.
Set MstrSummaryWks = ThisWorkbook.Worksheets("summary")
'change all formulas to text
MstrSummaryWks.Cells.Replace what:="=", replacement:="$$$$$=", _
lookat:=xlPart, MatchCase:=False

For fCtr = LBound(myNames) To UBound(myNames)
Set tempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
With tempWkbk
MstrSummaryWks.Copy _
after:=.Sheets(.Sheets.Count)
End With

ActiveSheet.Cells.Replace what:="$$$$$=", replacement:="=", _
lookat:=xlPart, MatchCase:=False

tempWkbk.Close savechanges:=True

Next fCtr

MstrSummaryWks.Cells.Replace what:="$$$$$=", replacement:="=", _
lookat:=xlPart, MatchCase:=False
End If

End Sub

This code goes into the workbook that has the master summary worksheet--but it's
not in that same folder as the other 83.
 
Dave actualy your idea about using a search and replace, changing the
formulas to text worked great. Wonderful idea. Thank you.

Steve

P.S. Man I have to say that is too cool of you to write out that much
code. Thanks so much for your time and effort. I think I can acutaly
use that code to do something else I have been trying to do. I'll let
you know how it turns out.

Thanks again.
Steve
 
You did 83 workbooks manually????

Wow!
Dave actualy your idea about using a search and replace, changing the
formulas to text worked great. Wonderful idea. Thank you.

Steve

P.S. Man I have to say that is too cool of you to write out that much
code. Thanks so much for your time and effort. I think I can acutaly
use that code to do something else I have been trying to do. I'll let
you know how it turns out.

Thanks again.
Steve
Click to expand...
 
No not really, but I have you to thank for that. I set up a Personal
macro that converted the formulas in the sheet to text by placing the
"=" sign at the end of the formula. Opened up ten or so sheets at a
time. Then had the macro grab the text from my original sheet, make a
new sheet called summary in the open workbook, copy the text there,
then convert the "######=" to "=######" so the formulas came back and
save the open sheet and go to the next sheet.

The beautiful thing about macros is that if you can imagine it you can
do it. You just have to have someone give you a push in the right
direction.
It took me about 20 minutes to do the whole thing.

Thanks again for your help.

Steve

P.S. Now all I have to do is get the company to invest in a DBA so I
can get rid of the 83 sheets in the first place.
 
Back
Top