how copy worksheets into workbook where worksheet makes reference to there?

  • Thread starter Thread starter Ian Elliott
  • Start date Start date
I

Ian Elliott

Thanks for any help.
I have a workbook that copies some worksheets from another
workbook into this workbook (macro code at end of this
message).
My code is such that it copies the worksheet to right
after the old version, deletes the old version, then
renames the new version the same name as the old version.
I.E.-copies in "Forecase", deletes "MF", then
renames "Forecast" to "MF"
I also have some worksheets in this workbook that
reference to the worksheets being copied (updated) in.
So there is a worksheet "SL" that has references to "MF".
I.E. a cell in "SL" might have =SUM(MF!H9) or something
like that.
But after running this macro, the cell in "SL" now
has:=#REF!H9. I think this is because the worksheet "MF"
was deleted (although later the worksheet "Forecast" that
was copied in was named "MF").
This is a workbook that is run monthly, and I don't want
to go back once a month to retype in (all) the worksheet
names in cells in "SL".
The reason I am using a worksheet copy method, rather than:
cells.select
selection.copy
....
selection.pastespecial

is because with the worksheet copy method, I can copy in
print setups, and it seems to be more accurate copying in
formats.
Does anyone have an idea how I can copy worksheets in and
still have a worksheet in the destination workbook that
references these worksheets? Thanks.

For a = 1 To 10
Workbooks.Open FileName:=Path(a) & File(a),
UpdateLinks:=0
Worksheets(WorkSheetNameThere(a)).Copy
after:=ThisWorkbook.Sheets(WorkSheetNameHere(a))
Sheets(WorkSheetNameHere(a)).Delete
Worksheets(WorkSheetNameThere(a)).Name =
WorkSheetNameHere(a)
Workbooks(File(a)).Close savechanges:=False
Next a
 
I think I would copy the cells and paste them right over the old sheet. Then
copy the print setup.

But if your formulas are all in cells in other worksheets, you could:

Edit|replace
all = with $$$$$=
this'll make you formulas Text. (you'll have to get all the sheets).

Then do your copy|delete|rename

and change $$$$$= back to just =
 
Back
Top