excel 根据主工作表中的单元格值，将“命名”工作表中的特定范围复制到主工作表中的特定范围

k4ymrczo 于 2023-01-03 发布在其他

关注(0)|答案(1)|浏览(186)

我的VBA代码使用了特定工作表中的Range（取决于主工作表中= year的单元格值）。
现在我想从，比方说工作表名为“2017”的范围。范围是特定的，在每个工作表中的每个数据年--〉总是=。范围（“O 14：S25”）
在我的主工作表“荣誉与薪金”中，我想粘贴工作表“2017”中的范围，并根据单元格= E18中的年份替换主工作表中的.Range（“C4：G15”）.值。

单元格区域包含字符串和/或数字值

我的VBA代码的基础：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Honoraires As Double
Dim Revenu As Variant
Dim LastValues As Variant
Dim Cotisations As Range
Dim k As Double
Dim drow As Double
Dim DValue As Double
Dim CountRow As Integer, SheetName As String, wsHVS As Worksheet

If Target.Address = "$B$23" Then
    Set wsHVS = ThisWorkbook.Worksheets("HONORAIRES VS. SALAIRE")
    Honoraires = wsHVS.Range("B22").Value
    SheetName = wsHVS.Range("E18").Value
    
    LastValues = 0
    CountRow = 4
    
    For Each Cell In ThisWorkbook.Worksheets(SheetName).Range("B5:B102").Cells
        If Cell.Value > Honoraires Then

            Worksheets("HONORAIRES VS. SALAIRE").Range("I22").Value = (Honoraires * ThisWorkbook.Worksheets(SheetName).Range("D" + CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value
            Worksheets("HONORAIRES VS. SALAIRE").Range("K22").Value = (Honoraires * ThisWorkbook.Worksheets(SheetName).Range("F" + CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value
            Worksheets("HONORAIRES VS. SALAIRE").Range("J22").Value = ((Honoraires - ThisWorkbook.Worksheets(SheetName).Range("B" + CStr(CountRow)).Value) * ThisWorkbook.Worksheets(SheetName).Range("I" + CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value
            Worksheets("HONORAIRES VS. SALAIRE").Range("L22").Value = ((Honoraires - ThisWorkbook.Worksheets(SheetName).Range("B" + CStr(CountRow)).Value) * ThisWorkbook.Worksheets(SheetName).Range("J" + CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value

            Exit For

        ElseIf LastValues < Honoraires Then

            CountRow = CountRow + 1
            LastValues = Cell.Value

        End If

    Next Cell

End If

“主表”

“2017年”表

在尝试了With和For代码行后，我感到完全迷失了方向。

excel

来源：https://stackoverflow.com/questions/72944227/copy-a-specific-range-from-a-named-sheet-to-specific-range-in-primary-sheet-de

1条答案

按热度按时间

vecaoik11#

我想这就是你完成任务所需要的。如果我有任何误解，请告诉我。

Sub LoadYear()

    Dim ReportYear As String
    ReportYear = Worksheets("Primary Sheet").Range("E18").Value
    
    If Not WorksheetExists(ReportYear) Then
        MsgBox "Selected Sheet does not appear to exist. " & _
            "Please find the sheet and ensure the sheet name is in ""yyyy"" format.", _
            vbOKOnly, "ERROR, Santa Clause not behaving."
        Exit Sub
    End If
    
    Worksheets("Primary Sheet").Range("C4:G15").Value = _
        Worksheets(ReportYear).Range("O14:S25").Value

End Sub
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean

    '>>> Borrowed from @Tim Williams on StackOverflow, thanks Tim.
    
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

我将使用一个加载按钮而不是一个工作表事件来简化它：