如何自動執行vb腳本

Question

我有這個代碼：

Sub NewYearData()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim r1 As Range, r2 As Range
    Dim addy As String, sh As Worksheet
    Dim shName As String
    
    Set wb1 = Workbooks("PreviousYear.xlsm")
    Set wb2 = Workbooks("CurrentYear2.xlsx")
    addy = "C16:AH18"
    
    For Each sh In wb1.Sheets
        shName = sh.Name
        Set r1 = sh.Range(addy)
        Set r2 = wb2.Sheets(shName).Range(addy)
        r1.Copy r2
    Next sh
    
End Sub

這是我必須複製的單元格的固定位置。在某些工作表中，位置不同，因此我必須找到引用外部文件和工作表的所有儲存格，例如： 'D:\Data\[Data_1990-2019.xlsx]Set01'!D105

問題：如何尋找包含引用的所有儲存格，並將這些儲存格複製到新檔案中？

Answer 1

以下程式碼是從文章中複製過來的
如何在Excel中找到並列出所有連結（外部引用）？

For Each xSheet In Worksheets
    Set xRg = xSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If xRg Is Nothing Then GoTo LblNext
    For Each xCell In xRg
        If InStr(1, xCell.Formula, "[") > 0 Then
            ...
       End If
    Next
LblNext:

您可以調整上述InStr調用以滿足您的需求。