Hi,
I have this long but easy to understand vba code. How can I increase the z value by one after every round of saving the file? Right now I have to manually type in the numbers where it says z=4. I want the z value to increase up to 300 starting from 4. Also I want the last line (ActiveWorkbook.SaveAs Filename:=pathname & SaveFile & “M34.xlsb”) to be changed corresponding to the z value. So if z=4 then the file should be saved as M34. If z=5 then the file should be saved as M35 and so on. So M should be always saved as M30+z. thanks a lot for your help guys. I really appreciate it. 


Sub Get_Max_And_Min()
Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\raw data\inbetween.xlsb")
Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\raw data\26.07.2004-26.07.2006.xlsb ")
   Dim x As Long, i As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row – 1
    i = 2
   z = 4
    j = 32 + z
    For x = 2 To Round(lr / (30+z), 0)
       Cells(x, 7).Formula = "A" & i
        Cells(x, 8).Formula = "B" & i
        Cells(x, 9).Formula = "C" & i
        Cells(x, 10).Formula = "Max(D" & i & ":D" & i + 30 + z & ")"
        Cells(x, 11).Formula = "Min(E" & i & ":E" & i + 30 + z & ")"
        Cells(x, 12).Formula = "F" & j
        i = i + 30 + z
        j = j + 30 + z
           Next x
           
           Columns("G:G").Select
    Selection.Replace What:="a", Replacement:="=a", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("H:H").Select
    Selection.Replace What:="b", Replacement:="=b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("I:I").Select
    Selection.Replace What:="c", Replacement:="=c", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("J:K").Select
    Selection.Replace What:="m", Replacement:="=m", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("L:L").Select
    Selection.Replace What:="f", Replacement:="=f", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Windows("26.07.2004-26.07.2006").Activate
Range("G2:L34936").Copy
Windows("inbetween").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("26.07.2004-26.07.2006").Close SaveChanges:=False
Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\raw data\26.07.2006-26.07.2008.xlsb ")
   e = 2 +z
    f = 32 + z
    For x = 2 To Round(lr / (30+z), 0)
       Cells(x, 7).Formula = "A" & e
        Cells(x, 8).Formula = "B" & e
        Cells(x, 9).Formula = "C" & e
        Cells(x, 10).Formula = "Max(D" & e & ":D" & e + 30 + z & ")"
        Cells(x, 11).Formula = "Min(E" & e & ":E" & e + 30 + z & ")"
        Cells(x, 12).Formula = "F" & f
        e = e + 30 + z
        f = f + 30 + z
           Next x
           
           Columns("G:G").Select
    Selection.Replace What:="a", Replacement:="=a", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("H:H").Select
    Selection.Replace What:="b", Replacement:="=b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("I:I").Select
    Selection.Replace What:="c", Replacement:="=c", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("J:K").Select
    Selection.Replace What:="m", Replacement:="=m", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("L:L").Select
    Selection.Replace What:="f", Replacement:="=f", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Windows("26.07.2006-26.07.2008").Activate
Range("G2:L34936").Copy
Windows("inbetween").Activate
Range("A34937").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("26.07.2006-26.07.2008").Close SaveChanges:=False
Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\raw data\26.07.2008-26.07.2010.xlsb ")
   g = 2 + z
    h = 32 + z
    For x = 2 To Round(lr / (30+z), 0)
       Cells(x, 7).Formula = "A" & g
        Cells(x, 8).Formula = "B" & g
        Cells(x, 9).Formula = "C" & g
        Cells(x, 10).Formula = "Max(D" & g & ":D" & g + 30 + z & ")"
        Cells(x, 11).Formula = "Min(E" & g & ":E" & g + 30 + z & ")"
        Cells(x, 12).Formula = "F" & h
        g = g + 30 + z
        h = h + 30 + z
           Next x
           
           Columns("G:G").Select
    Selection.Replace What:="a", Replacement:="=a", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("H:H").Select
    Selection.Replace What:="b", Replacement:="=b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("I:I").Select
    Selection.Replace What:="c", Replacement:="=c", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("J:K").Select
    Selection.Replace What:="m", Replacement:="=m", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("L:L").Select
    Selection.Replace What:="f", Replacement:="=f", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Windows("26.07.2008-26.07.2010").Activate
Range("G2:L34936").Copy
Windows("inbetween").Activate
Range("A69872").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("26.07.2008-26.07.2010").Close SaveChanges:=False
Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\raw data\26.07.2010-26.07.2012.xlsb ")
  a = 2 + z
    b = 32 + z
    For x = 2 To Round(lr / (30+z), 0)
       Cells(x, 7).Formula = "A" & a
        Cells(x, 8).Formula = "B" & a
        Cells(x, 9).Formula = "C" & a
        Cells(x, 10).Formula = "Max(D" & a & ":D" & a + 30 + z & ")"
        Cells(x, 11).Formula = "Min(E" & a & ":E" & a + 30 + z & ")"
        Cells(x, 12).Formula = "F" & b
        a = a + 30 + z
        b = b + 30 + z
           Next x
           
           Columns("G:G").Select
    Selection.Replace What:="a", Replacement:="=a", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("H:H").Select
    Selection.Replace What:="b", Replacement:="=b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("I:I").Select
    Selection.Replace What:="c", Replacement:="=c", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("J:K").Select
    Selection.Replace What:="m", Replacement:="=m", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("L:L").Select
    Selection.Replace What:="f", Replacement:="=f", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Windows("26.07.2010-26.07.2012").Activate
Range("G2:L34936").Copy
Windows("inbetween").Activate
Range("A104807").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("26.07.2010-26.07.2012").Close SaveChanges:=False
Application.Workbooks.Open ("C:\Users\wolfgang\Desktop\raw data\26.07.2012-26.07.2014.xlsb ")
c = 2 + z
    d = 32 + z
    For x = 2 To Round(lr / (30+z), 0)
       Cells(x, 7).Formula = "A" & c
        Cells(x, 8).Formula = "B" & c
        Cells(x, 9).Formula = "C" & c
        Cells(x, 10).Formula = "Max(D" & c & ":D" & c + 30 + z & ")"
        Cells(x, 11).Formula = "Min(E" & c & ":E" & c + 30 + z & ")"
        Cells(x, 12).Formula = "F" & d
        c = c + 30 + z
        d = d + 30 + z
           Next x
           
           Columns("G:G").Select
    Selection.Replace What:="a", Replacement:="=a", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("H:H").Select
    Selection.Replace What:="b", Replacement:="=b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("I:I").Select
    Selection.Replace What:="c", Replacement:="=c", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("J:K").Select
    Selection.Replace What:="m", Replacement:="=m", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   Columns("L:L").Select
    Selection.Replace What:="f", Replacement:="=f", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Windows("26.07.2012-26.07.2014").Activate
Range("G2:L34936").Copy
Windows("inbetween").Activate
Range("A139742").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("26.07.2012-26.07.2014").Close SaveChanges:=False
Windows("inbetween").Activate
On Error Resume Next
    Range("A1:F200000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.SaveAs Filename:=pathname & SaveFile & “M34.xlsb”
ActiveWorkbook.Close 
End Sub