在新工作表中保留参考单元格-Java 学习之路

我基本上试图在一张纸上进行操作，以便在另一张纸上进行镜像 . 在Sheet1上复制并粘贴A1到B1？在Sheet2上复制并粘贴A1到B1 . 问题是，它总是需要参考活动单元格，我无法弄清楚如何以可用格式保存活动单元格的地址 .

这是我想用非常简单的英语完成的场景：

其中Cell是Sheet1上的ActiveCell，在Sheet2上的Cell行上插入行（例如，如果Sheet1！A1是活动单元格，则在Sheet2第1行下面插入行） .
在Sheet1上：复制ActiveCell.Row并在ActiveCell.Row下面插入 .
On Sheet2：执行相同的操作，但是在Sheet2上的相应行上，除了我想将它粘贴到我从步骤1新插入的行中 . 因此，如果我复制了Sheet1第1行并将其插入第2步中的第1行，我想要复制Sheet2第1行并将其粘贴到步骤1的新行中 .
返回Sheet1，使用InputBox从用户获取值，在Range中插入该值（“D”＆（ActiveCell.Row））

除了Sheet2部分之外，我有这个全部工作，如果我在Sheet1上复制/插入之前无法获得该行，它会破坏公式 . 我已经手动完成了这些步骤，如果我可以对其进行编码，则一切正常 .

Sub Button18_Click（）

Dim Row_Source As Range
Dim WS As Worksheet, WS2 As Worksheet
Dim Day_Num As String
Dim Day_Dest As Range
Dim PRL As String
Dim Address As String
Dim RowNum As Long

Dim Cell As Range
Set Cell = ActiveCell ' just in case you'll decide to give-up on the "bad practice" of using ActiveCell
RowNum = Cell.Row

Set WS = ThisWorkbook.Sheets("Protocols")
Set WS2 = ThisWorkbook.Sheets("Formulas")

With WS
    PRL = .Range("B" & RowNum).Value

    Day_Num = InputBox("Please enter a day number to add to: " & PRL, "Add New Day")
    If Day_Num <> "" Then
        Set Row_Source = .Rows(RowNum)
    End If
End With

With WS2
    If Day_Num <> "" Then
        Row_Source.Offset(1).Insert Shift:=xlDown
        Application.CutCopyMode = False
    End If
End With

With WS
    If Day_Num <> "" Then
        Row_Source.Copy

        Row_Source.Offset(1).Insert Shift:=xlDown
        Application.CutCopyMode = False

        .Range("D" & RowNum + 1).Value = Day_Num
    End If
End With

With WS2
    If Day_Num <> "" Then
        Set Row_Source = .Rows(RowNum)
        Row_Source.Copy

        Row_Source.Offset(1).Select
        Row_Source.PasteSpecial
        Application.CutCopyMode = False
    End If
End With

结束子

2 回答

您正在寻找类似下面的代码：

Sub Button18_Click()

    Dim Row_Source As Range
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Day_Num As String
    Dim Day_Dest As Range
    Dim PRL As String
    Dim Address As String
    Dim RowNum As Long

    Dim Cell As Range
    Set Cell = ActiveCell ' just in case you'll decide to give-up on the "bad practice" of using ActiveCell
    RowNum = Cell.Row

    Set WS = ThisWorkbook.Sheets("Protocols")
    Set WS2 = ThisWorkbook.Sheets("Formulas")

    With WS
        PRL = .Range("B" & RowNum).Value

        Day_Num = InputBox("Please enter a day number to add to: " & PRL, "Add New Day")
        If Day_Num <> "" Then
            Set Row_Source = .Rows(RowNum)
            Row_Source.Copy

            Row_Source.Offset(1).Insert Shift:=xlDown
            Application.CutCopyMode = False

            .Range("D" & RowNum + 1).Value = Day_Num
        End If
    End With

    With WS2
        Set Row_Source = .Rows(RowNum)
        Row_Source.Copy

        Row_Source.Offset(1).Insert Shift:=xlDown
        Application.CutCopyMode = False
    End With

End Sub

回复于 2024-05-03T15:48:07+08:00

这是完成它的代码 . 如果没有Shai Rado，我不可能完成这项工作，而大部分功劳都应该去 . 这完全适用于规范：

Sub Button18_Click()

    Dim Row_Source As Range
    Dim WS As Worksheet, WS2 As Worksheet
    Dim Day_Num As String
    Dim Day_Dest As Range
    Dim PRL As String
    Dim RowNum As Long
    Dim Cell As Range

    Set Cell = ActiveCell ' just in case you'll decide to give-up on the "bad practice" of using ActiveCell
    RowNum = Cell.Row

    Set WS = ThisWorkbook.Sheets("Protocols")
    Set WS2 = ThisWorkbook.Sheets("Formulas")

    With WS
        PRL = .Range("B" & RowNum).Value

        Day_Num = InputBox("Please enter a day number to add to: " & PRL, "Add New Day")
        If Day_Num <> "" Then
            Set Row_Source = .Rows(RowNum)
        End If
    End With

    With WS2
        If Day_Num <> "" Then
            Set Row_Source = .Rows(RowNum)
            Row_Source.Offset(1).Insert Shift:=xlDown
            Application.CutCopyMode = False
        End If
    End With

    With WS
        If Day_Num <> "" Then
            Set Row_Source = .Rows(RowNum)
            Row_Source.Copy

            Row_Source.Offset(1).Insert Shift:=xlDown
            Application.CutCopyMode = False

            .Range("D" & RowNum + 1).Value = Day_Num
        End If
    End With

    With WS2
        Set Row_Source = .Rows(RowNum)
        Row_Source.Copy

        Row_Source.Offset(1).PasteSpecial

        Application.CutCopyMode = False
    End With

End Sub

回复于 2024-05-03T15:48:07+08:00

在新工作表中保留参考单元格

2 回答

相关问题