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