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
asked Nov 3, 2021 at 10:34
\$\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\$ Commented Nov 3, 2021 at 10:37

2 Answers 2

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.

answered Nov 3, 2021 at 21:57
\$\endgroup\$
4
  • \$\begingroup\$ Wow, thanks that's a really good idea. I'm a beginner in vba but I try to implement it! :) \$\endgroup\$ Commented Nov 4, 2021 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\$ Commented Nov 4, 2021 at 12:09
  • \$\begingroup\$ No, sorry. Sometimes it’s easier to just start from scratch =) \$\endgroup\$ Commented Nov 4, 2021 at 12:16
  • \$\begingroup\$ Okey, I find it out somehow than :D \$\endgroup\$ Commented Nov 4, 2021 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.

answered Nov 13, 2021 at 3:28
\$\endgroup\$

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.