VBA 将公式(在多行中)拖到使用的最后一行

Question

我有来自 O -> X 列的公式，需要它们将它们拖到使用的最后一行。以下是我正在使用的当前代码:

Dim wkb As Workbook
Dim wkbFrom As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim path As String, FilePart As String
Dim TheFile
Dim loc As String
Dim Lastrow As Long

Set wkb = ThisWorkbook
loc = shPivot.Range("E11").Value
path = shPivot.Range("E12").Value
FilePart = Trim(shPivot.Range("E13").Value)
TheFile = Dir(path & "*" & FilePart & ".xls")
Set wkbFrom = Workbooks.Open(loc & path & TheFile & FilePart)
Set wks = wkbFrom.Sheets("SUPPLIER_01_00028257_KIK CUSTOM")
Set rng = wks.Range("A2:N500")

'Copies range from report generated to share drive and pastes into the current week tab of open order report
rng.Copy wkb.Sheets("Current Week").Range("A4")

With ActiveSheet

    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("O4:X4").AutoFill .Range("O4:X4").Resize(Lastrow)

End With

代码 Lastrow 没有将公式拖下来

Answer 1

您可以在 VBA 中进行这样的自动填充(使用宏录制验证)

Range("O1:X1").Select
Selection.AutoFill Destination:=Range("O1:X25"), Type:=xlFillDefault

现在您有了此代码作为使用的基础，您可以在语法中使用您喜欢的任何变量，如下所示:

Range("O1:X1").Select
Selection.AutoFill Destination:=Range("O1:X" & Lastrow), Type:=xlFillDefault