Мне нужно удалить листы из книги без разрыва ссылок на формулы; Есть ли способ сделать это с помощью кода или настроек?
8 ответов
3
Мой бесплатный Mappit! Надстройка, доступная по адресу http://www.experts-exchange.com/A_2613.html, имеет в качестве одного из своих выходов карту, на которой показаны отношения листа
Вы можете использовать это, чтобы определить, где могут возникнуть потенциальные проблемы при удалении листа.
1
Скопируйте все формулы. Вставьте как формулы в другую область (например, лист, чтобы сохранить их, которые затем можно будет скрыть). На листе с формулой резервных копий найдите «=», замените на «|» (или любой другой редко используемый символ). Всякий раз, когда вам нужны формулы, вам нужно только вставить их туда, где они должны быть, затем найдите "|" и заменить на «=» (не включать ни одного из "кавычек").
Это позволяет легко исправить формулы, которые вы, возможно, нарушили. Я обычно ломаю свою, удаляя рабочий лист с ячейками, на которые ссылаются формулы. Я использую эту технику, чтобы исправить таблицы, сгенерированные запросом мощности, когда он не заполняет таблицу с тем же порядком столбцов, что и запрос. Процесс состоит в том, чтобы загрузить только в соединение (при этом удаляются данные, на которые ссылались мои формулы), затем загрузить в рабочую таблицу, принудительно перестроив таблицу с тем же порядком столбцов, что и в запросе.
0
Если вы жестко программируете конкретный лист, это было бы невозможно. Вы подразумеваете, что хотели бы, чтобы зависимость была перенесена на другой лист?
0
Как указано выше, невозможно удалить лист без разрыва ссылки. Однако вот несколько вариантов в зависимости от ваших обстоятельств:
- Специальное копирование / вставка: значения, чтобы сохранить результат формулы и удалить лист.
- Скройте лист и защитите книгу вместо удаления листа.
- Измените ссылки на лист в другой книге.
0
это спустя 4 года, но я хотел бы оставить здесь свое решение на случай, если оно поможет кому-то, имеющему ту же проблему
Процесс:
- Вырежьте (CTRL+X) ячейку и ее содержимое, на которое ссылается ваша формула с 1-го листа (лист, который будет удален, например: Лист!$ A $ 1)
- Вставьте вырезанную ячейку на 2-й лист в новом или том же месте (лист, который будет сохранен, напр .: Вставьте его в Лист2!A1)
- Сделайте это для всех ячеек, которые должны быть переданы
- Проверьте формулы, чтобы убедиться, что они ссылаются на 2-й лист (Лист2! вместо Sheet1!)
- Удалить 1-й лист
- При необходимости переименуйте 2-й лист в то же имя, что и 1-й лист, который у вас был
Надеюсь это поможет
0
Вот как я делал это в прошлом. Я использую его, когда удаляю лист во время выполнения кода. Общий процесс заключается в следующем с конкретной процедурой ниже. Обратите внимание, что вам нужно определить публичную переменную в верхней части модуля.
Option Explicit
Public arFormulasToKeep as Variant
Sub SomeProcessThatRemovesReference()
'Procedure that stores formulas in public variable
Call StoreValidationFormulas(TheRangeToStoreFormulas)
'Your codes execution here
'Sheet deletes that breaks reference
'
'
'
'Return formulas back to range
Call ReturnFormulasBackToRange(TheRangeToStoreFormulas)
End Sub 'SomeProcessThatRemovesAsReference()
'----------------------------------------------------------------------------
Sub StoreValidationFormulas(rngToStoreFormulas As Range)
'Call this before you delete the sheet or break the reference
'Public variable that will store formulas in memory until you need to insert again
arFormulasToKeep = ReturnFormulasFromRange(rngToStoreFormulas)
End Sub
Public Function ReturnFormulasFromRange(rRange As Range) As Variant
'This will go and store formulas only into array that is the exact dimension/order as the range you have passed, you could alter this funciton to return everything
Dim arFXs
Dim i, j As Long
ReDim arFXs(1 To rRange.Rows.Count, 1 To rRange.Columns.Count)
For i = 1 To rRange.Rows.Count
For j = 1 To rRange.Columns.Count
If rRange.Cells(i, j).HasFormula = True Then
arFXs(i, j) = rRange.Cells(i, j).FormulaR1C1
Else
arFXs(i, j) = ""
End If
Next j
Next i
'Pass back to function
ReturnFormulasFromRange = arFXs
End Function
Sub ReturnFormulasBackToRange(rngToReturnFormulas as range)
'''Now return stored formulas back to range
Dim i, j As Long
For i = 1 To rngToReturnFormulas.Rows.Count
For j = 1 To rngToReturnFormulas.Columns.Count
If arFormulasToKeep (i, j) <> "" Then
rngToReturnFormulas.Cells(i, j).FormulaR1C1 = arFormulasToKeep(i, j)
End If
Next j
Next i
End Sub
0
Я знаю, что опаздываю на вечеринку, но я нашел полезный и простой обходной путь. Допустим, вы пытаетесь VLOOKUP в лист, который постоянно заменяется (Sheet2). Это ваша формула:
=VLOOKUP(A1,Sheet2!$A:$B,2,FALSE)
Когда вы удалите Sheet2, ссылка будет прервана. Но если вы продолжите заменять Sheet2 на листы, названные так же, сделайте следующее:
На случайной ячейке, скажем, AA1, напишите:
Sheet2!$A:$B
Не используйте знак равенства. Вы хотите это как чистый текст.
Затем измените свой VLOOKUP на:
=VLOOKUP(A1,INDIRECT($AA$1),2,FALSE)
Чистый текст не подвержен удалению ссылок, поэтому это должно сделать ваш код безопасным.
0
Я знаю, что опоздал с ответом на этот вопрос, у меня была та же проблема, и для решения этой проблемы я сделал следующее:
- Создана фальшивая первая строка, содержащая мою формулу как на листе формулы, так и на листе, где я извлекаю данные
- Сделал эту первую строку скрытой в обоих листах
- Теперь, когда пользователь удаляет данные с помощью моего скрипта, формула снова вынимается из скрытой строки, которая не может быть разбита, поскольку она связана со строкой, которая не будет удалена. (Обратите внимание, что эту линию лучше всего защитить, чтобы люди не могли случайно удалить ее)
- Если вы не работаете с vba, вы можете сделать то же самое вручную: показать, снять защиту и снова перетащить формулу вниз