文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >从选定的excel文件中复制最后125行并粘贴到已使用的文件中

问从选定的excel文件中复制最后125行并粘贴到已使用的文件中
EN

Code Review用户
提问于 2021-11-03 10:34:54
回答 2查看 72关注 0票数 1

我的任务是从excel工作簿中获取最后125行。

行从第17行开始，并一直持续到可以。

这是我的密码：

代码语言：javascript
复制
Sub Get_Data_From_File()

    Const START_ROW As Long = 17
    Const NUM_ROWS As Long = 124
    
    Dim FileToOpen As String
    Dim wb As Workbook, ws As Worksheet, wsDest As Worksheet
    Dim LastRow As Long, FirstRow As Long
    Dim LastRows As Range
    
    FileToOpen = Application.GetOpenFilename("Excel files (*.xls*), *.xls*", _
                                            Title:="Válassza ki a fájlt!")
                                            
    If FileToOpen = "False" Then Exit Sub 'if a file is not selected close the window and stop the macro
    
    Set wsDest = ActiveSheet             'pasting here; or specfy some other sheet... anyway its working only with the active sheet
    Set wb = Workbooks.Open(FileToOpen, ReadOnly:=True)
    Set ws = wb.Worksheets("SMI_650_Lxy")       'or whatever sheet you need
    
    LastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row 'find last row
    If LastRow < START_ROW Then LastRow = START_ROW
    
    FirstRow = IIf(LastRow - NUM_ROWS >= START_ROW, LastRow - NUM_ROWS, START_ROW) 'find first row
    
    Debug.Print "FirstRow" & vbTab & FirstRow 'test int the immediate windows
    Debug.Print "LastRow" & vbTab & LastRow
    Debug.Print "START_ROW" & vbTab & START_ROW
    
    'copy ranges into the same cells
    ws.Range("C" & FirstRow & ":C" & LastRow).Copy wsDest.Cells(START_ROW, "C")
    ws.Range("F" & FirstRow & ":F" & LastRow).Copy wsDest.Cells(START_ROW, "F")
    ws.Range("M" & FirstRow & ":M" & LastRow).Copy 'Formula
    wsDest.Range("M17:M141").PasteSpecial Paste:=xlPasteValues
    ws.Range("P" & FirstRow & ":P" & LastRow).Copy wsDest.Cells(START_ROW, "P")
    ws.Range("S" & FirstRow & ":S" & LastRow).Copy wsDest.Cells(START_ROW, "S")
    ws.Range("V" & FirstRow & ":V" & LastRow).Copy wsDest.Cells(START_ROW, "V")
    ws.Range("Y" & FirstRow & ":Y" & LastRow).Copy wsDest.Cells(START_ROW, "Y")
    ws.Range("AF" & FirstRow & ":AF" & LastRow).Copy 'Formula
    wsDest.Range("AF17:AF141").PasteSpecial Paste:=xlPasteValues
    ws.Range("AM" & FirstRow & ":AM" & LastRow).Copy 'Formula
    wsDest.Range("AM17:AM141").PasteSpecial Paste:=xlPasteValues
    ws.Range("AP" & FirstRow & ":AP" & LastRow).Copy wsDest.Cells(START_ROW, "AP")
    ws.Range("AS" & FirstRow & ":AS" & LastRow).Copy wsDest.Cells(START_ROW, "AS")
    ws.Range("AV" & FirstRow & ":AV" & LastRow).Copy wsDest.Cells(START_ROW, "AV")
    ws.Range("AY" & FirstRow & ":AY" & LastRow).Copy wsDest.Cells(START_ROW, "AY")
    ws.Range("BB" & FirstRow & ":BB" & LastRow).Copy wsDest.Cells(START_ROW, "BB")
    ws.Range("BE" & FirstRow & ":BE" & LastRow).Copy wsDest.Cells(START_ROW, "BE")
    ws.Range("BL" & FirstRow & ":BL" & LastRow).Copy 'Formula
    wsDest.Range("BL17:BL141").PasteSpecial Paste:=xlPasteValues
    ws.Range("BS" & FirstRow & ":BS" & LastRow).Copy 'Formula
    wsDest.Range("BS17:BS141").PasteSpecial Paste:=xlPasteValues
    ws.Range("BV" & FirstRow & ":BV" & LastRow).Copy wsDest.Cells(START_ROW, "BV")
    ws.Range("BZ" & FirstRow & ":BZ" & LastRow).Copy wsDest.Cells(START_ROW, "BZ")
    ws.Range("CD" & FirstRow & ":CD" & LastRow).Copy wsDest.Cells(START_ROW, "CD")
    ws.Range("CH" & FirstRow & ":CH" & LastRow).Copy wsDest.Cells(START_ROW, "CH")
    ws.Range("CK" & FirstRow & ":CK" & LastRow).Copy wsDest.Cells(START_ROW, "CK")
    ws.Range("CN" & FirstRow & ":CN" & LastRow).Copy wsDest.Cells(START_ROW, "CN")
    ws.Range("CQ" & FirstRow & ":CQ" & LastRow).Copy wsDest.Cells(START_ROW, "CQ")
    ws.Range("CT" & FirstRow & ":CT" & LastRow).Copy wsDest.Cells(START_ROW, "CT")
    ws.Range("CW" & FirstRow & ":CW" & LastRow).Copy wsDest.Cells(START_ROW, "CW")
    ws.Range("CZ" & FirstRow & ":CZ" & LastRow).Copy wsDest.Cells(START_ROW, "CZ")
    ws.Range("DC" & FirstRow & ":DC" & LastRow).Copy wsDest.Cells(START_ROW, "DC")
    ws.Range("DF" & FirstRow & ":DF" & LastRow).Copy wsDest.Cells(START_ROW, "DF")
    
    wsDest.Range("17:141").Rows.Hidden = True 'Hide the row which is used for the data migration
    
    wb.Close False 'no save
    
    'Add the Formulas (note you need the US-format when using .Formula
    '   or you can use your local format with .FormulaLocal
    
    ''insert the formula to calculate avarage
    Range("C5").FormulaLocal = "=ÁTLAG(INDEX(C17:C2025;HOL.VAN(MAX(C17:C2025);C17:C2025;1)):INDEX(C17:C2025;MAX(1;HOL.VAN(MAX(C17:C2025);C17:C2025;1)-124)))"
    Range("F5").FormulaLocal = "=ÁTLAG(INDEX(F17:F2025;HOL.VAN(MAX(F17:F2025);F17:F2025;1)):INDEX(F17:F2025;MAX(1;HOL.VAN(MAX(F17:F2025);F17:F2025;1)-124)))"
    Range("M5").FormulaLocal = "=ÁTLAG(INDEX(M17:M2025;HOL.VAN(MAX(M17:M2025);M17:M2025;1)):INDEX(M17:M2025;MAX(1;HOL.VAN(MAX(M17:M2025);M17:M2025;1)-124)))"
    Range("P5").FormulaLocal = "=ÁTLAG(INDEX(P17:P2025;HOL.VAN(MAX(P17:P2025);P17:P2025;1)):INDEX(P17:P2025;MAX(1;HOL.VAN(MAX(P17:P2025);P17:P2025;1)-124)))"
    Range("S5").FormulaLocal = "=ÁTLAG(INDEX(S17:S2025;HOL.VAN(MAX(S17:S2025);S17:S2025;1)):INDEX(S17:S2025;MAX(1;HOL.VAN(MAX(S17:S2025);S17:S2025;1)-124)))"
    Range("V5").FormulaLocal = "=ÁTLAG(INDEX(V17:V2025;HOL.VAN(MAX(V17:V2025);V17:V2025;1)):INDEX(V17:V2025;MAX(1;HOL.VAN(MAX(V17:V2025);V17:V2025;1)-124)))"
    Range("Y5").FormulaLocal = "=ÁTLAG(INDEX(Y17:Y2025;HOL.VAN(MAX(Y17:Y2025);Y17:Y2025;1)):INDEX(Y17:Y2025;MAX(1;HOL.VAN(MAX(Y17:Y2025);Y17:Y2025;1)-124)))"
    Range("AF5").FormulaLocal = "=ÁTLAG(INDEX(AF17:AF2025;HOL.VAN(MAX(AF17:AF2025);AF17:AF2025;1)):INDEX(AF17:AF2025;MAX(1;HOL.VAN(MAX(AF17:AF2025);AF17:AF2025;1)-124)))"
    Range("AM5").FormulaLocal = "=ÁTLAG(INDEX(AM17:AM2025;HOL.VAN(MAX(AM17:AM2025);AM17:AM2025;1)):INDEX(AM17:AM2025;MAX(1;HOL.VAN(MAX(AM17:AM2025);AM17:AM2025;1)-124)))"
    Range("AP5").FormulaLocal = "=ÁTLAG(INDEX(AP17:AP2025;HOL.VAN(MAX(AP17:AP2025);AP17:AP2025;1)):INDEX(AP17:AP2025;MAX(1;HOL.VAN(MAX(AP17:AP2025);AP17:AP2025;1)-124)))"
    Range("AS5").FormulaLocal = "=ÁTLAG(INDEX(AS17:AS2025;HOL.VAN(MAX(AS17:AS2025);AS17:AS2025;1)):INDEX(AS17:AS2025;MAX(1;HOL.VAN(MAX(AS17:AS2025);AS17:AS2025;1)-124)))"
    Range("AV5").FormulaLocal = "=ÁTLAG(INDEX(AV17:AV2025;HOL.VAN(MAX(AV17:AV2025);AV17:AV2025;1)):INDEX(AV17:AV2025;MAX(1;HOL.VAN(MAX(AV17:AV2025);AV17:AV2025;1)-124)))"
    Range("AY5").FormulaLocal = "=ÁTLAG(INDEX(AY17:AY2025;HOL.VAN(MAX(AY17:AY2025);AY17:AY2025;1)):INDEX(AY17:AY2025;MAX(1;HOL.VAN(MAX(AY17:AY2025);AY17:AY2025;1)-124)))"
    Range("BB5").FormulaLocal = "=ÁTLAG(INDEX(BB17:BB2025;HOL.VAN(MAX(BB17:BB2025);BB17:BB2025;1)):INDEX(BB17:BB2025;MAX(1;HOL.VAN(MAX(BB17:BB2025);BB17:BB2025;1)-124)))"
    Range("BE5").FormulaLocal = "=ÁTLAG(INDEX(BE17:BE2025;HOL.VAN(MAX(BE17:BE2025);BE17:BE2025;1)):INDEX(BE17:BE2025;MAX(1;HOL.VAN(MAX(BE17:BE2025);BE17:BE2025;1)-124)))"
    Range("BL5").FormulaLocal = "=ÁTLAG(INDEX(BL17:BL2025;HOL.VAN(MAX(BL17:BL2025);BL17:BL2025;1)):INDEX(BL17:BL2025;MAX(1;HOL.VAN(MAX(BL17:BL2025);BL17:BL2025;1)-124)))"
    Range("BS5").FormulaLocal = "=ÁTLAG(INDEX(BS17:BS2025;HOL.VAN(MAX(BS17:BS2025);BS17:BS2025;1)):INDEX(BS17:BS2025;MAX(1;HOL.VAN(MAX(BS17:BS2025);BS17:BS2025;1)-124)))"
    Range("BV5").FormulaLocal = "=ÁTLAG(INDEX(BV17:BV2025;HOL.VAN(MAX(BV17:BV2025);BV17:BV2025;1)):INDEX(BV17:BV2025;MAX(1;HOL.VAN(MAX(BV17:BV2025);BV17:BV2025;1)-124)))"
    Range("BZ5").FormulaLocal = "=ÁTLAG(INDEX(BZ17:BZ2025;HOL.VAN(MAX(BZ17:BZ2025);BZ17:BZ2025;1)):INDEX(BZ17:BZ2025;MAX(1;HOL.VAN(MAX(BZ17:BZ2025);BZ17:BZ2025;1)-124)))"
    Range("CD5").FormulaLocal = "=ÁTLAG(INDEX(CD17:CD2025;HOL.VAN(MAX(CD17:CD2025);CD17:CD2025;1)):INDEX(CD17:CD2025;MAX(1;HOL.VAN(MAX(CD17:CD2025);CD17:CD2025;1)-124)))"
    Range("CH5").FormulaLocal = "=ÁTLAG(INDEX(CH17:CH2025;HOL.VAN(MAX(CH17:CH2025);CH17:CH2025;1)):INDEX(CH17:CH2025;MAX(1;HOL.VAN(MAX(CH17:CH2025);CH17:CH2025;1)-24)))"
    Range("CK5").FormulaLocal = "=ÁTLAG(INDEX(CK17:CK2025;HOL.VAN(MAX(CK17:CK2025);CK17:CK2025;1)):INDEX(CK17:CK2025;MAX(1;HOL.VAN(MAX(CK17:CK2025);CK17:CK2025;1)-24)))"
    Range("CN5").FormulaLocal = "=ÁTLAG(INDEX(CN17:CN2025;HOL.VAN(MAX(CN17:CN2025);CN17:CN2025;1)):INDEX(CN17:CN2025;MAX(1;HOL.VAN(MAX(CN17:CN2025);CN17:CN2025;1)-24)))"
    Range("CQ5").FormulaLocal = "=ÁTLAG(INDEX(CQ17:CQ2025;HOL.VAN(MAX(CQ17:CQ2025);CQ17:CQ2025;1)):INDEX(CQ17:CQ2025;MAX(1;HOL.VAN(MAX(CQ17:CQ2025);CQ17:CQ2025;1)-24)))"
    Range("CT5").FormulaLocal = "=ÁTLAG(INDEX(CT17:CT2025;HOL.VAN(MAX(CT17:CT2025);CT17:CT2025;1)):INDEX(CT17:CT2025;MAX(1;HOL.VAN(MAX(CT17:CT2025);CT17:CT2025;1)-24)))"
    Range("CW5").FormulaLocal = "=ÁTLAG(INDEX(CW17:CW2025;HOL.VAN(MAX(CW17:CW2025);CW17:CW2025;1)):INDEX(CW17:CW2025;MAX(1;HOL.VAN(MAX(CW17:CW2025);CW17:CW2025;1)-24)))"
    Range("CZ5").FormulaLocal = "=ÁTLAG(INDEX(CZ17:CZ2025;HOL.VAN(MAX(CZ17:CZ2025);CZ17:CZ2025;1)):INDEX(CZ17:CZ2025;MAX(1;HOL.VAN(MAX(CZ17:CZ2025);CZ17:CZ2025;1)-24)))"
    Range("DC5").FormulaLocal = "=ÁTLAG(INDEX(DC17:DC2025;HOL.VAN(MAX(DC17:DC2025);DC17:DC2025;1)):INDEX(DC17:DC2025;MAX(1;HOL.VAN(MAX(DC17:DC2025);DC17:DC2025;1)-24)))"
    Range("DF5").FormulaLocal = "=ÁTLAG(INDEX(DF17:DF2025;HOL.VAN(MAX(DF17:DF2025);DF17:DF2025;1)):INDEX(DF17:DF2025;MAX(1;HOL.VAN(MAX(DF17:DF2025);DF17:DF2025;1)-24)))"
    
    'insert the formula to calculate dispersion
    Range("C6").FormulaLocal = "=SZÓRÁS(INDEX(C17:C2025;HOL.VAN(MAX(C17:C2025);C17:C2025;1)):INDEX(C17:C2025;MAX(1;HOL.VAN(MAX(C17:C2025);C17:C2025;1)-124)))"
    Range("F6").FormulaLocal = "=SZÓRÁS(INDEX(F17:F2025;HOL.VAN(MAX(F17:F2025);F17:F2025;1)):INDEX(F17:F2025;MAX(1;HOL.VAN(MAX(F17:F2025);F17:F2025;1)-124)))"
    Range("M6").FormulaLocal = "=SZÓRÁS(INDEX(M17:M2025;HOL.VAN(MAX(M17:M2025);M17:M2025;1)):INDEX(M17:M2025;MAX(1;HOL.VAN(MAX(M17:M2025);M17:M2025;1)-124)))"
    Range("P6").FormulaLocal = "=SZÓRÁS(INDEX(P17:P2025;HOL.VAN(MAX(P17:P2025);P17:P2025;1)):INDEX(P17:P2025;MAX(1;HOL.VAN(MAX(P17:P2025);P17:P2025;1)-124)))"
    Range("S6").FormulaLocal = "=SZÓRÁS(INDEX(S17:S2025;HOL.VAN(MAX(S17:S2025);S17:S2025;1)):INDEX(S17:S2025;MAX(1;HOL.VAN(MAX(S17:S2025);S17:S2025;1)-124)))"
    Range("V6").FormulaLocal = "=SZÓRÁS(INDEX(V17:V2025;HOL.VAN(MAX(V17:V2025);V17:V2025;1)):INDEX(V17:V2025;MAX(1;HOL.VAN(MAX(V17:V2025);V17:V2025;1)-124)))"
    Range("Y6").FormulaLocal = "=SZÓRÁS(INDEX(Y17:Y2025;HOL.VAN(MAX(Y17:Y2025);Y17:Y2025;1)):INDEX(Y17:Y2025;MAX(1;HOL.VAN(MAX(Y17:Y2025);Y17:Y2025;1)-124)))"
    Range("AF6").FormulaLocal = "=SZÓRÁS(INDEX(AF17:AF2025;HOL.VAN(MAX(AF17:AF2025);AF17:AF2025;1)):INDEX(AF17:AF2025;MAX(1;HOL.VAN(MAX(AF17:AF2025);AF17:AF2025;1)-124)))"
    Range("AM6").FormulaLocal = "=SZÓRÁS(INDEX(AM17:AM2025;HOL.VAN(MAX(AM17:AM2025);AM17:AM2025;1)):INDEX(AM17:AM2025;MAX(1;HOL.VAN(MAX(AM17:AM2025);AM17:AM2025;1)-124)))"
    Range("AP6").FormulaLocal = "=SZÓRÁS(INDEX(AP17:AP2025;HOL.VAN(MAX(AP17:AP2025);AP17:AP2025;1)):INDEX(AP17:AP2025;MAX(1;HOL.VAN(MAX(AP17:AP2025);AP17:AP2025;1)-124)))"
    Range("AS6").FormulaLocal = "=SZÓRÁS(INDEX(AS17:AS2025;HOL.VAN(MAX(AS17:AS2025);AS17:AS2025;1)):INDEX(AS17:AS2025;MAX(1;HOL.VAN(MAX(AS17:AS2025);AS17:AS2025;1)-124)))"
    Range("AV6").FormulaLocal = "=SZÓRÁS(INDEX(AV17:AV2025;HOL.VAN(MAX(AV17:AV2025);AV17:AV2025;1)):INDEX(AV17:AV2025;MAX(1;HOL.VAN(MAX(AV17:AV2025);AV17:AV2025;1)-124)))"
    Range("AY6").FormulaLocal = "=SZÓRÁS(INDEX(AY17:AY2025;HOL.VAN(MAX(AY17:AY2025);AY17:AY2025;1)):INDEX(AY17:AY2025;MAX(1;HOL.VAN(MAX(AY17:AY2025);AY17:AY2025;1)-124)))"
    Range("BB6").FormulaLocal = "=SZÓRÁS(INDEX(BB17:BB2025;HOL.VAN(MAX(BB17:BB2025);BB17:BB2025;1)):INDEX(BB17:BB2025;MAX(1;HOL.VAN(MAX(BB17:BB2025);BB17:BB2025;1)-124)))"
    Range("BE6").FormulaLocal = "=SZÓRÁS(INDEX(BE17:BE2025;HOL.VAN(MAX(BE17:BE2025);BE17:BE2025;1)):INDEX(BE17:BE2025;MAX(1;HOL.VAN(MAX(BE17:BE2025);BE17:BE2025;1)-124)))"
    Range("BL6").FormulaLocal = "=SZÓRÁS(INDEX(BL17:BL2025;HOL.VAN(MAX(BL17:BL2025);BL17:BL2025;1)):INDEX(BL17:BL2025;MAX(1;HOL.VAN(MAX(BL17:BL2025);BL17:BL2025;1)-124)))"
    Range("BS6").FormulaLocal = "=SZÓRÁS(INDEX(BS17:BS2025;HOL.VAN(MAX(BS17:BS2025);BS17:BS2025;1)):INDEX(BS17:BS2025;MAX(1;HOL.VAN(MAX(BS17:BS2025);BS17:BS2025;1)-124)))"
    Range("BV6").FormulaLocal = "=SZÓRÁS(INDEX(BV17:BV2025;HOL.VAN(MAX(BV17:BV2025);BV17:BV2025;1)):INDEX(BV17:BV2025;MAX(1;HOL.VAN(MAX(BV17:BV2025);BV17:BV2025;1)-124)))"
    Range("BZ6").FormulaLocal = "=SZÓRÁS(INDEX(BZ17:BZ2025;HOL.VAN(MAX(BZ17:BZ2025);BZ17:BZ2025;1)):INDEX(BZ17:BZ2025;MAX(1;HOL.VAN(MAX(BZ17:BZ2025);BZ17:BZ2025;1)-124)))"
    Range("CD6").FormulaLocal = "=SZÓRÁS(INDEX(CD17:CD2025;HOL.VAN(MAX(CD17:CD2025);CD17:CD2025;1)):INDEX(CD17:CD2025;MAX(1;HOL.VAN(MAX(CD17:CD2025);CD17:CD2025;1)-124)))"
    Range("CH6").FormulaLocal = "=SZÓRÁS(INDEX(CH17:CH2025;HOL.VAN(MAX(CH17:CH2025);CH17:CH2025;1)):INDEX(CH17:CH2025;MAX(1;HOL.VAN(MAX(CH17:CH2025);CH17:CH2025;1)-24)))"
    Range("CK6").FormulaLocal = "=SZÓRÁS(INDEX(CK17:CK2025;HOL.VAN(MAX(CK17:CK2025);CK17:CK2025;1)):INDEX(CK17:CK2025;MAX(1;HOL.VAN(MAX(CK17:CK2025);CK17:CK2025;1)-24)))"
    Range("CN6").FormulaLocal = "=SZÓRÁS(INDEX(CN17:CN2025;HOL.VAN(MAX(CN17:CN2025);CN17:CN2025;1)):INDEX(CN17:CN2025;MAX(1;HOL.VAN(MAX(CN17:CN2025);CN17:CN2025;1)-24)))"
    Range("CQ6").FormulaLocal = "=SZÓRÁS(INDEX(CQ17:CQ2025;HOL.VAN(MAX(CQ17:CQ2025);CQ17:CQ2025;1)):INDEX(CQ17:CQ2025;MAX(1;HOL.VAN(MAX(CQ17:CQ2025);CQ17:CQ2025;1)-24)))"
    Range("CT6").FormulaLocal = "=SZÓRÁS(INDEX(CT17:CT2025;HOL.VAN(MAX(CT17:CT2025);CT17:CT2025;1)):INDEX(CT17:CT2025;MAX(1;HOL.VAN(MAX(CT17:CT2025);CT17:CT2025;1)-24)))"
    Range("CW6").FormulaLocal = "=SZÓRÁS(INDEX(CW17:CW2025;HOL.VAN(MAX(CW17:CW2025);CW17:CW2025;1)):INDEX(CW17:CW2025;MAX(1;HOL.VAN(MAX(CW17:CW2025);CW17:CW2025;1)-24)))"
    Range("CZ6").FormulaLocal = "=SZÓRÁS(INDEX(CZ17:CZ2025;HOL.VAN(MAX(CZ17:CZ2025);CZ17:CZ2025;1)):INDEX(CZ17:CZ2025;MAX(1;HOL.VAN(MAX(CZ17:CZ2025);CZ17:CZ2025;1)-24)))"
    Range("DC6").FormulaLocal = "=SZÓRÁS(INDEX(DC17:DC2025;HOL.VAN(MAX(DC17:DC2025);DC17:DC2025;1)):INDEX(DC17:DC2025;MAX(1;HOL.VAN(MAX(DC17:DC2025);DC17:DC2025;1)-24)))"
    Range("DF6").FormulaLocal = "=SZÓRÁS(INDEX(DF17:DF2025;HOL.VAN(MAX(DF17:DF2025);DF17:DF2025;1)):INDEX(DF17:DF2025;MAX(1;HOL.VAN(MAX(DF17:DF2025);DF17:DF2025;1)-24)))"
    
    'to freeze the rows above the 17th row
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .SplitRow = 16
        .FreezePanes = True
    End With
    
End Sub
vba
excel
EN

回答 2

Code Review用户

发布于 2021-11-03 21:57:32

我不知道这是否适用于您的特定项目，但我的第一反应是创建一些隐藏的行，其值如下

第1行:指定是否应复制值的标志

第2行:指定是否应复制公式的标志

脚本可以读取这些行并作出决定，而不是硬编码应该复制哪些值和公式。

当前解决方案的问题是，如果有人添加或移除列，则需要重做整个脚本。

票数 1
EN

Code Review用户

发布于 2021-11-13 03:28:45

您的问题不清楚，但是按照标题，下面的代码将从源表的底部将125行(如果没有那么多行的话更少)转移到目标表。

代码语言：javascript
复制
Sub GetRows()
    ' 310
    
    Const StartRow As Long = 17
    Const NumRows As Long = 125         ' number of rows from the end
    
    Dim WbS     As Workbook             ' Source file
    Dim WsS     As Worksheet            ' Source sheet
    Dim RngS    As Range                ' Source range
    Dim Cs      As Long                 ' Columns count in WsS
    Dim RsS     As Long                 ' first source row
    Dim RlS     As Long                 ' last source row in WsS
    Dim WbD     As Workbook             ' Destination file
    Dim WsD     As Worksheet            ' Destination sheet
    Dim Target  As Range                ' Destination cell

    Set WbS = ThisWorkbook              ' use your existing code to open whatever workbook
    Set WsS = WbS.Worksheets(1)         ' specify whichever in WsSsheet you want
    With WsS
        ' find the last used column in StartRow
        Cs = .Cells(StartRow, .Columns.Count).End(xlToLeft).Column
        ' find the last used row in column A
        RlS = .Cells(.Rows.Count, "A").End(xlUp).Row
        RsS = WorksheetFunction.Max(RlS - NumRows + 1, StartRow)
        
        ' select the last max 125 rows starting from StartRow
        Set RngS = Range(.Cells(RsS, "A"), .Cells(RlS, Cs))
        Debug.Print RngS.Address(0, 0)
    End With
    
    Set WbD = ThisWorkbook              ' specify whichever workbook you have open
    Set WsD = WbS.Worksheets(2)         ' specify whichever sheet in WsD you want
    Set Target = WsD.Cells(1, 1)        ' specify the first cell to copy the 125 rows to
    RngS.Copy Destination:=Target
End Sub

在我的代码中，源代码、工作簿和目标是相同的。我认为您可以使用您已经拥有的部分代码来更改代码。重点是定义一个范围并复制/粘贴它。

在此过程中，公式可能会更改它们引用的单元格的地址。这是因为行号改变了。在我的示例中，第一个源行是第17行，但是第一个目标行是1。如果这是一个问题，您可能首先粘贴到相同的行号，然后删除目标表中的行。

您没有为您想要的公式提供任何解释，编写公式与复制125行不同。但基本上，您需要一个公式和一个循环，将其复制到125行:3行代码。公式的组成将是另一个问题--显然与这个公式的标题无关。

票数 1
EN
页面原文内容由Code Review提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://codereview.stackexchange.com/questions/269690

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档