Hi,
I am not good with VBA. However i tried to record a macro for a daily activity. Basically i get an excel, I look for a cell in column A which reads "Rate for today"...above that cell i delete all the rows and columns.
I filter for colored cells : Color model RGB. Red 192, Green 192 and Blue 192. and add two formulas in column O and P. One is just "=H5-H4" and other is simple vlookup which pulls data from next tab. I copy the formula to all the filters rows. Below is the macro code which i recorded. Is there a way to make it short and error proof?
Thanks
Sub Macro1()
'
' Macro1 Macro
'
'
Rows("1:71").Select
Range("B1").Activate
Selection.Delete Shift:=xlUp
Rows("3:3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$AA$66").AutoFilter Field:=2, Criteria1:=RGB(192, _
192, 192), Operator:=xlFilterCellColor
Range("O3").Select
Windows("VM for Rego.xlsx").Activate
Range("N1:O1").Select
Selection.Copy
Windows("VD.XLS").Activate
ActiveSheet.Paste
Columns("O:O").EntireColumn.AutoFit
Range("O7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-7]-R[-1]C[-7]"
Range("O7").Select
Selection.Copy
Range("O11:O66").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("P7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],List!C[-13]:C[-12],2,0)"
Range("P7").Select
Selection.Copy
Range("P7:P66").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.ShowAllData
ActiveWindow.SmallScroll Down:=6
Windows("VM for Rego.xlsx ").Activate
Windows("VD.XLS").Activate
ActiveWindow.SmallScroll Down:=-3
Range("P14,P33").Select
Range("P33").Activate
Selection.Font.Bold = True
Range("O14").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("O33").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("O34").Select
End Sub
I am not good with VBA. However i tried to record a macro for a daily activity. Basically i get an excel, I look for a cell in column A which reads "Rate for today"...above that cell i delete all the rows and columns.
I filter for colored cells : Color model RGB. Red 192, Green 192 and Blue 192. and add two formulas in column O and P. One is just "=H5-H4" and other is simple vlookup which pulls data from next tab. I copy the formula to all the filters rows. Below is the macro code which i recorded. Is there a way to make it short and error proof?
Thanks
Sub Macro1()
'
' Macro1 Macro
'
'
Rows("1:71").Select
Range("B1").Activate
Selection.Delete Shift:=xlUp
Rows("3:3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$AA$66").AutoFilter Field:=2, Criteria1:=RGB(192, _
192, 192), Operator:=xlFilterCellColor
Range("O3").Select
Windows("VM for Rego.xlsx").Activate
Range("N1:O1").Select
Selection.Copy
Windows("VD.XLS").Activate
ActiveSheet.Paste
Columns("O:O").EntireColumn.AutoFit
Range("O7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-7]-R[-1]C[-7]"
Range("O7").Select
Selection.Copy
Range("O11:O66").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("P7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],List!C[-13]:C[-12],2,0)"
Range("P7").Select
Selection.Copy
Range("P7:P66").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.ShowAllData
ActiveWindow.SmallScroll Down:=6
Windows("VM for Rego.xlsx ").Activate
Windows("VD.XLS").Activate
ActiveWindow.SmallScroll Down:=-3
Range("P14,P33").Select
Range("P33").Activate
Selection.Font.Bold = True
Range("O14").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("O33").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("O34").Select
End Sub
