如何通过VBA在指定单元格区域旁的单元格自动填充公式

你的现有代码已完成新项目工作表的创建、命名，以及在「Client Projects Overview」工作表C列空白单元格添加超链接的功能。要实现D、E、F、G列自动填充上方单元格的公式（避免出现#Ref!错误），可以直接基于Emrange定位目标区域，无需依赖Selection.AutoFill的选中状态，以下是修改后的完整方案：

修改后的完整代码 #

Public Sub CopySheetAndRenameByCell()
    Dim newName As String, Emrange As Range, wsNew As Worksheet, wb As Workbook
    Dim wsIndex As Worksheet
    Dim targetRow As Long ' 存储目标行号
    
    newName = InputBox("输入新项目名称", "复制工作表", ActiveCell.Value)
    
    If newName <> "" Then
        Set wb = ThisWorkbook
        wb.Worksheets("Project Sheet BLANK").Copy _
                      After:=wb.Worksheets(wb.Worksheets.Count)
        Set wsNew = wb.Worksheets(wb.Worksheets.Count)
        On Error Resume Next ' 忽略重命名错误
        wsNew.Name = newName
        On Error GoTo 0     ' 恢复错误捕获
        
        Set wsIndex = wb.Worksheets("Client Projects Overview")
        Set Emrange = wsIndex.Range("C" & Rows.Count).End(xlUp).Offset(1)
        targetRow = Emrange.Row ' 获取当前空白单元格的行号
        
        ' 添加超链接并设置字体样式
        wsIndex.Hyperlinks.Add Anchor:=Emrange, _
                           Address:="", SubAddress:="'" & wsNew.Name & "'!A1", _
                           TextToDisplay:=wsNew.Name
        Emrange.Font.Underline = xlUnderlineStyleNone
        Emrange.Font.ColorIndex = xlAutomatic
        Emrange.Font.Name = "Century Gothic"
        Emrange.Font.Size = 10
        
        ' 核心：复制上一行D-G列的公式到当前行
        With wsIndex
            .Range("D" & targetRow - 1 & ":G" & targetRow - 1).Copy
            .Range("D" & targetRow & ":G" & targetRow).PasteSpecial Paste:=xlPasteFormulas
            Application.CutCopyMode = False ' 清除复制状态
        End With
        
        ' 检查工作表重命名是否成功
        If wsNew.Name <> newName Then
            MsgBox "提供的名称'" & newName & "'不是有效的工作表名称！", vbExclamation
        End If
    End If
End Sub

关键修改说明 #

1. 新增行号变量：通过Emrange.Row获取C列空白单元格的行号，精准定位D-G列的目标区域。
2. 公式复制逻辑：直接复制上一行D-G列的公式并粘贴到当前行，公式会自动更新引用至新创建的项目工作表（因新表结构与空白模板一致，引用关系会自动适配）。
3. 可选：用AutoFill实现：若偏好和手动拖动填充一致的效果，可替换上述复制粘贴代码为以下内容：

' AutoFill写法
wsIndex.Range("D" & targetRow - 1 & ":G" & targetRow - 1).AutoFill _
    Destination:=wsIndex.Range("D" & targetRow - 1 & ":G" & targetRow), _
    Type:=xlFillDefault

内容的提问来源于stack exchange，提问作者Steve101