1
\$\begingroup\$

My task was to get the last 125 rows from an excel workbook.

The rows are started from the 17th row and it goes until it can.

Here's my code:

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
\$\endgroup\$
1
  • \$\begingroup\$ The formulas in English: Szórás: STDEV(INDEX(DF17:DF2025;MATCH(MAX(DF17:DF2025);DF17:DF2025;1)):INDEX(DF17:DF2025;MAX(1;MATCH(MAX(DF17:DF2025);DF17:DF2025;1)-24)))" Átlag: AVARAGE(INDEX(DF17:DF2025;MATCH(MAX(DF17:DF2025);DF17:DF2025;1)):INDEX(DF17:DF2025;MAX(1;MATCH(MAX(DF17:DF2025);DF17:DF2025;1)-24)))" \$\endgroup\$
    – Rockraizer
    Nov 3 '21 at 10:37
1
\$\begingroup\$

I don't know if this will work for your particular project, but my first instinct would be to create some hidden rows with values like the following

enter image description here

Row 1: Flags to specify if the value should be copied
Row 2: Flags to specify if the formula should be copied

Rather than hardcoding which values and formulas should be copied, your script can read these rows and decide.

The problem with your current solution is that if anyone adds or removes a column, then the whole script needs to be redone.

\$\endgroup\$
4
  • \$\begingroup\$ Wow, thanks that's a really good idea. I'm a beginner in vba but I try to implement it! :) \$\endgroup\$
    – Rockraizer
    Nov 4 '21 at 10:04
  • \$\begingroup\$ I stucked with it can you help me how can I make it? I needs to get the rows from the 17th rows under the formulas. I thinked about it before I started to code that to insert a statement for that I don't know how to begin. Maybe I could say that's the beta version of my code. :( \$\endgroup\$
    – Rockraizer
    Nov 4 '21 at 12:09
  • \$\begingroup\$ No, sorry. Sometimes it’s easier to just start from scratch =) \$\endgroup\$
    – jdt
    Nov 4 '21 at 12:16
  • \$\begingroup\$ Okey, I find it out somehow than :D \$\endgroup\$
    – Rockraizer
    Nov 4 '21 at 12:17
1
\$\begingroup\$

Your question lacks clarity but going by your title the code below will transfer 125 rows from the bottom of the source sheet (less if there aren't as many rows) to a destination sheet.

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

In my code source workbook and target are the same. I think you can change the code using parts of what you already have. The point is that you define one range and copy/paste it.

In this process formulas may change the addresses of cells they reference. That is because row numbers change. In my example the first source row is row 17 but the first target row is 1. If this is a problem for you you might first paste to the same row number and later remove rows in the destination sheet.

You offer no explanation for the formulas you seem to want and writing formulas isn't the same as copying 125 rows. But basically you need one formula and a loop that copies it to 125 rows: 3 lines of code. The composition of the formula would be another question - obviously not connected to the title of this one.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.