VBA Code:
Sub MULTIPLE_OPTION_MAC()
'
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "Security AC ID"
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],12)"
countrow = Application.WorksheetFunction.CountIf(Range("A:A"), "<>")
Selection.AutoFill Destination:=Range("C2:C" & countrow)
Range("C2:C" & countrow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=4, Criteria1:=">0", _
Operator:=xlAnd
Range("C1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("H1").Select
ActiveSheet.Paste
Columns("H:H").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=4
Range("E1").Select
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=5, Criteria1:=">0", _
Operator:=xlAnd
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("L1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("M1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=5
Cells.Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=6
Range("F1").Select
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=6, Criteria1:=">0", _
Operator:=xlAnd
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("P1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("Q1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=5
Cells.Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=7
Range("G1").Select
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=7, Criteria1:=">0", _
Operator:=xlAnd
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("G1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("U1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=7
Cells.Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Range("N1").Select
ActiveCell.FormulaR1C1 = "OPT SEQ"
Range("N2").Select
ActiveCell.FormulaR1C1 = "2"
Range("N2").Select
Selection.Copy
Range("M2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("N2").Select
Application.CutCopyMode = False
countrowopt2 = Application.WorksheetFunction.CountIf(Range("L:L"), "<>")
Selection.AutoFill Destination:=Range("N2:N" & countrowopt2)
Range("J1").Select
ActiveCell.FormulaR1C1 = "OPT SEQ"
Range("J2").Select
ActiveCell.FormulaR1C1 = "1"
Range("J2").Select
Selection.Copy
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("J2").Select
Application.CutCopyMode = False
countrowopt1 = Application.WorksheetFunction.CountIf(Range("H:H"), "<>")
Selection.AutoFill Destination:=Range("J2:J" & countrowopt1)
Range("J2:J" & countrowopt1).Select
Range("R1").Select
ActiveCell.FormulaR1C1 = "OPT SEQ"
Range("R2").Select
ActiveCell.FormulaR1C1 = "3"
Range("R2").Select
Selection.Copy
Range("Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("R2").Select
Application.CutCopyMode = False
countrowopt3 = Application.WorksheetFunction.CountIf(Range("P:P"), "<>")
Selection.AutoFill Destination:=Range("R2:R" & countrowopt3)
Range("R2:R" & countrowopt1).Select
Range("V1").Select
ActiveCell.FormulaR1C1 = "OPT SEQ"
Range("V2").Select
ActiveCell.FormulaR1C1 = "4"
Range("V2").Select
Selection.Copy
Range("U2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("V2").Select
Application.CutCopyMode = False
countrowopt4 = Application.WorksheetFunction.CountIf(Range("T:T"), "<>")
Selection.AutoFill Destination:=Range("V2:V" & countrowopt4)
Range("V2:V" & countrowopt1).Select
Range("K1").Select
ActiveCell.FormulaR1C1 = "CR"
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & countrowopt1)
Range("K2:K" & countrowopt1).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("O1").Select
ActiveCell.FormulaR1C1 = "CR"
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & countrowopt2)
Range("O2:O" & countrowopt2).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S1").Select
ActiveCell.FormulaR1C1 = "CR"
Range("S2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S" & countrowopt3)
Range("S2:S" & countrowopt1).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("W1").Select
ActiveCell.FormulaR1C1 = "CR"
Range("W2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
Range("W2").Select
Selection.AutoFill Destination:=Range("W2:W" & countrowopt4)
Range("W2:W" & countrowopt1).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("H2:J" & countrowopt1).Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("E2").Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("L2:N" & countrowopt2).Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("A2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("E2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("P2:R" & countrowopt3).Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("A2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("E2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("T2:V" & countrowopt3).Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("A2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("OPTION TEST MAC.xlsm").Activate
Range("W2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FILECRO V11_TEST1.csv").Activate
Range("E2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.NumberFormat = "0"
Range("B2").Select
Columns("A:E").EntireColumn.AutoFit
MsgBox ("done")
End Sub
THIS MACRO WAS CREATED TO PULL OUT DATA FROM ANOTHER EXCEL WITH OPTIONS....4 OPTION....CAN I USE IT FOR DATA WITH 3 OPTIONS ....HOW CAN I SKIP COLUMNS IF NO VALUE?
Last edited by a moderator: