KEMBAR78
EW VBscript Rev01 | PDF | Decorative Arts | Infographics
Scribd
Upload
39 views10 pages

EW VBscript Rev01

This macro copies cell values from one sheet to another, applies filters, copies and pastes formulas across ranges, and formats cells based on text values. Specifically, it 1) copies data between sheets; 2) filters data based on criteria; and 3) copies formulas down columns to analyze vibration data.

Uploaded by

Muhammad Fahmmi Bin Mahmud
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
39 views10 pages

EW VBscript Rev01

This macro copies cell values from one sheet to another, applies filters, copies and pastes formulas across ranges, and formats cells based on text values. Specifically, it 1) copies data between sheets; 2) filters data based on criteria; and 3) copies formulas down columns to analyze vibration data.

Uploaded by

Muhammad Fahmmi Bin Mahmud
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 10

Sub addd()

'
' addd Macro
'

'
Sheets("Coding").Select
Range("D6").Select
Selection.Copy
Sheets("VibesReadings").Select
Range("D139").Select
ActiveSheet.Paste
Range("B139").Select
End Sub
Sub Button_Evaluate_1()
'
' Button_Evaluate_1 Macro
'

'
Columns("B:Z").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("H1").Select
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=7, Criteria1:="mm/Sec"
Sheets("Coding").Select
Range("D76").Select
Selection.Copy
Sheets("VibesReadings").Select
Range("D1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Range("D1:D3002").Select
ActiveSheet.Paste
Range("B1").Select
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=1, Criteria1:=Array( _
"E1A", "E1H", "E1V", "E2A", "E2H", "E2V"), Operator:=xlFilterValues
Range("D1").Select
Application.CutCopyMode = False
Application.FormulaBarHeight = 2
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<3,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>12,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>6,""ALARM1"",""OK"")))))))"
Range("D1").Select
Selection.Copy
Range("D1:D3002").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=1, Criteria1:=Array( _
"A1A", "A1H", "A1V", "A2A", "A2H", "A2V"), Operator:=xlFilterValues
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<3,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>11,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>5,""ALARM1"",""OK"")))))))"
Range("D1").Select
Selection.Copy
Range("D1:D3002").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=1, Criteria1:=Array( _
"G1A", "G1H", "G1V", "G2A", "G2H", "G2V", "G2X", "G2Y", "G3H", "G3V"),
Operator:= _
xlFilterValues
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Range("D1:D3002").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=1
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=7, Criteria1:="G-s"
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<0.2,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>2,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>1,""ALARM1"",""OK"")))))))"
Range("D1").Select
Selection.Copy
Range("D1:D3002").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=7, Criteria1:="Microns"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[1]<10,""OK"",IF(SUM(Laz2Mths!RC[8],RC[6])>100,""ALARM2
>100%"",IF(RC[6]>100,""ALARM2"",IF(RC[1]>50,""ALARM2"", IF(RC[6]>100,""ALARM2
>100%"", IF(RC[6]>50,""ALARM1 >50%"",IF(RC[1]>40,""ALARM1"",""OK"")))))))"
Range("D1").Select
Selection.Copy
Range("D1:D3002").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=7
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Columns("F:F").Select
ActiveSheet.Paste
Range("B1").Select
'
' Button_Evaluate_7 Macro
'

'
Application.Goto Reference:="R1C7"
Range("G1:M3002").Select
Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.SmallScroll Down:=-78
Application.Goto Reference:="R1C5"
Range("G1:M3002").Select
Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.Range("$B$1:$Z$1150").AutoFilter Field:=2, Criteria1:="-"
Range("N1").Select
ActiveCell.FormulaR1C1 = "=MAXA(RC[-6]:RC[-1])"
Columns("N:N").Select
Selection.NumberFormat = "[$-en-MY,1]d/m/yy;@"
Columns("H:M").Select
Range("N1").Select
Selection.Copy
Range("N1:N3001").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=2
Range("N1").Select
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=13, Criteria1:="="
Range("N2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("N2").Select
Selection.Copy
Range("N2:N3001").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=13
Range("B1").Select
'
' Button_Evaluate_4 Macro
'
Columns("D:D").Select
Selection.FormatConditions.Add Type:=xlTextString, String:=">100%", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:=">50%", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B1").Select
'
' Button_Evaluate_3 Macro
'
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=2, Criteria1:="-"
Sheets("Coding").Select
Range("D1").Select
Selection.Copy
Sheets("VibesReadings").Select
Range("D15").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D46").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D66").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D77").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D88").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D6").Select
Selection.Copy
Sheets("VibesReadings").Select
Range("D99").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D7").Select
Selection.Copy
Sheets("VibesReadings").Select
Range("D110").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D121").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D132").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D164").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D196").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D232").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D268").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D304").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D340").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D362").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D384").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D406").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D423").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D440").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D21").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D457").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D468").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D23").Select
Selection.Copy
Sheets("VibesReadings").Select
Range("D479").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D490").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D25").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D512").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D534").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D27").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D580").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D28").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D600").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D620").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D666").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D31").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D686").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D32").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D706").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D750").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D770").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D35").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D790").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D36").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D812").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D37").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D834").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D38").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D856").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D878").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D40").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D900").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D41").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D922").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D42").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D946").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D43").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D970").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D44").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D999").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D45").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1050").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D46").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1101").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D47").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1152").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1203").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D49").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1225").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D50").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1247").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D51").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1285").Select
ActiveSheet.Paste
Sheets("Coding").Select
Range("D52").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VibesReadings").Select
Range("D1307").Select
ActiveSheet.Paste
ActiveSheet.Range("$B$1:$Z$3001").AutoFilter Field:=13
Range("B1").Select
Range("D1:D3001").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""H"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""M"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""L"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B1").Select
End Sub

You might also like