how to combine 2 macros?

L

LodiM1988

New Member
Joined
Nov 22, 2013
Messages
2
Hi all,

Although I have no experience with macros, I'm trying to have some of my laboratory analysis done automatically. Now I've got 2 macros which I would like to combine.

-macro 1: copies all the excel workbooks in the same folder as the current excel file is located in. Each workbook is copied to a sheet in a new excel master file. I found this macro on the internet. Each excel file only contains one sheet.

-macro 2 is a macro I recorded myself. It contains the analysis that should be done on all the workbooks in the folder, before transporting them in the master file.

-If I understood right, macro 1 opens all the excel files in the folder one by one, copies the data into a sheet in the master file and closes the file again. I wondered if macro 1 is also able to do the calculations from macro 2 for me.

So I want macro 1 to:
-open a file from the folder
-run the calculation in this file (macro 2)
-paste the data from the file into the master file
-close the file again
-continue with the next file.

Another solution could be that macro2 is able to run the calculations on all the sheets in the master file by activating it ones. The amount of sheets can change per analysis.

I would greatly appreciate any help with this. Thanks in advance.

Lodi.

here the 2 macros:

macro 1
Code: 
Sub Consolidate_Workbooks()
     
     
    Dim wbPath As String
    Dim wbNewPath As String
     
    Dim Wb     As Workbook
    Dim nBook  As Workbook
    Dim sFile  As String
    Dim zName  As String
    Dim ws     As Worksheet
    Dim newbook As String
     
    wbPath = ThisWorkbook.Path
    wbNewPath = ThisWorkbook.Path
     
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    Set nBook = Workbooks.Add
    zName = wbNewPath & "/" & "New-Workbook.xls"
    nBook.SaveAs Filename:=zName
     
    newbook = "New-workbook.xls"
     
    sFile = Dir(wbPath & "/" & "*.xls")
    Do While sFile <> ""
        If sFile <> ThisWorkbook.Name And sFile <> Workbooks(newbook).Name Then
            Set Wb = Workbooks.Open(wbPath & "/" & sFile)
            For Each ws In Wb.Sheets
                With Wb
                    .Sheets(ws.Name).Copy After:=Workbooks(newbook).Sheets(Workbooks(newbook).Sheets.Count)
                End With
                Wb.Close SaveChanges:=False
            Next
             
        End If
        sFile = Dir
    Loop
     
     'Delete worksheets 1,2,3
    Dim vaNames As Variant
    vaNames = Array(1, 2, 3)
    Worksheets(vaNames).Delete
     
    Worksheets(1).Select
     
     'nBook.Close SaveChanges:=True
     
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
     
    Set Wb = Nothing
    Set nBook = Nothing
     
     
     
     
End Sub
macro 2
Code: 
Sub analysis1()
'
' Macro4 Macro
' analysis z stock
'

'
    Cells.Select
    Range("E1").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "area"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "id"
    Range("A2:B2").Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "1 = sample"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-4]>0,1,0)"
    Range("E3").Select
    Selection.AutoFill Destination:=Range("E3:E200"), Type:=xlFillDefault
    Range("E3:E200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-4]>0,1,0)"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-4]>0,1,0)"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-4]>0,1,0)"
    Range("E2").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("E:E").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("G5").Select
    Columns("E:E").ColumnWidth = 8.67
    Range("F3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F4").Select
    Selection.AutoFill Destination:=Range("F4:F200"), Type:=xlFillDefault
    Range("F4:F200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,1)"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,0)"
    Range("F4").Select
    Selection.AutoFill Destination:=Range("F3:F4"), Type:=xlFillDefault
    Range("F3:F4").Select
    Range("F3").Select
    Selection.ClearContents
    Range("F4").Select
    Selection.AutoFill Destination:=Range("F4:F200"), Type:=xlFillDefault
    Range("F4:F200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,0)"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=IF((SUM(R[-3]C[-1]:R[-1]C[-1]))>0,1,0)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "1 = bg"
    Range("G2").Select
    Selection.Font.Bold = True
    Columns("F:F").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("G:G").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("G4").Select
    Selection.AutoFill Destination:=Range("G4:G200"), Type:=xlFillDefault
    Range("G4:G200").Select
    Range("I196").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("E:E,G:G").Select
    Range("G1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Cells.FormatConditions.Delete
    Range("E:E,G:G").Select
    Range("G1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("E:E,G:G").Select
    Range("G1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("F:F").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
    End With
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "area"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "ID/area"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "(ID/area)-bg"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "average"
    Range("N2").Select
    Columns("L:L").ColumnWidth = 10
    Columns("L:L").ColumnWidth = 12.33
    Range("I2:M2").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("I1:M1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "samples"
    Range("I1:M1").Select
    Selection.Font.Bold = True
    Range("I1:M2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("O1:P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "background"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "area"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("O1:P2").Select
    Range("O2").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("R1:T1").Select
    ActiveCell.FormulaR1C1 = "average bg"
    Range("R1:T1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("O1:P1").Select
    ActiveCell.FormulaR1C1 = "background (bg)"
    Range("R1:T2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "area"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "ID/area"
    Range("R1:T2").Select
    Selection.Font.Bold = True
    Range("R2:T2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]=1,RC[-8],"" "")"
    Range("I3").Select
    Selection.AutoFill Destination:=Range("I3:I200"), Type:=xlFillDefault
    Range("I3:I200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=1,RC[-8],"" "")"
    Range("J3").Select
    Selection.AutoFill Destination:=Range("J3:J200"), Type:=xlFillDefault
    Range("J3:J200").Select
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=1,(RC[-1]/RC[-2]),"" "")"
    Range("K3").Select
    Selection.AutoFill Destination:=Range("K3:K200"), Type:=xlFillDefault
    Range("K3:K200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=1,(RC[-1]/RC[-2]),"" "")"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=1,(RC[-1]/RC[-2]),"" "")"
    Range("O4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-8]=1,RC[-14],"" "")"
    Range("O4").Select
    Selection.AutoFill Destination:=Range("O4:O200"), Type:=xlFillDefault
    Range("O4:O200").Select
    ActiveWindow.ScrollRow = 172
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 158
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("P4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-9]=1,RC[-14],"" "")"
    Range("P4").Select
    Selection.AutoFill Destination:=Range("P4:P200"), Type:=xlFillDefault
    Range("P4:P200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("R3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:R[197]C[-3])"
    Range("S3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:R[197]C[-3])"
    Range("S4").Select
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("T3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Range("R3:T3").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-7]=1,(RC[-1]-R3C20),"" "")"
    Range("L3").Select
    Selection.AutoFill Destination:=Range("L3:L200"), Type:=xlFillDefault
    Range("L3:L200").Select
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 155
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 115
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[197]C[-1])"
    Range("M3").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("M2:M3").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1").Select
End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Something like

in Macro 1

make (copy) the amendments in RED


Code: 
            Set Wb = Workbooks.Open(wbPath & "/" & sFile)
            For Each ws In Wb.Sheets
 [COLOR=#ff0000]               ws.Activate    'active the current sheet
                Call analysis1  'call the analysis macro which works on the active sheet 
[/COLOR]               With Wb
                    .Sheets(ws.Name).Copy After:=Workbooks(newbook).Sheets(Workbooks(newbook).Sheets.Count)
                End With
                Wb.Close SaveChanges:=False
            Next


also in macro 2

you can delete all the ActiveWindow.ScrollRow = nnnn lines its just the macro recording you moving about the sheet,
makes the macro much longer for no benefit
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
344
RoryA
RoryA
P
  • Question Question
Help with Vba Code
Replies
3
Views
464
Coyotex3
C
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
1K
cstuder
C
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
577
Alex Blakenburg
A
K
  • Solved
formating question
Replies
3
Views
312
Micron
M

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top