Excel VBA

 Sub Step1()


    Sheets.Add(After:=ActiveSheet).Name = "NewDATASHEET"

    Sheets("DATASHEET").Select

    ActiveWindow.FreezePanes = True

    Columns("A:C").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("NewDATASHEET").Select

    ActiveSheet.Paste

    Range("D1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Content"

    Range("D2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-3],""|"",RC[-2])"

    Selection.AutoFill Destination:=Range("D2:D8000")

    Range("E2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=StringToBase64(RC[-2])"

    Selection.AutoFill Destination:=Range("E2:E8000")

    Selection.AutoFilter

    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:="<>"

    

    

    Sheets.Add(After:=ActiveSheet).Name = "NewSheet2"

    Sheets("DATASHEET").Select

    Range("B2").Select

    Range("E:E,D:D,A:A").Select

    Range("A1").Activate

    Selection.Copy

    Sheets("NewSheet2").Select

    ActiveSheet.Paste

    Range("D1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Content"

    Range("D2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-3],""|"",RC[-2])"

    Selection.AutoFill Destination:=Range("D2:D8000")

    Range("E2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=StringToBase64(RC[-2])"

    Selection.AutoFill Destination:=Range("E2:E8000")

    Selection.AutoFilter

    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:="<>"

    

    Sheets.Add(After:=ActiveSheet).Name = "NewSheet3"

    Sheets("DATASHEET").Select

    Range("F:G,A:A").Select

    Range("A1").Activate

    Selection.Copy

    Sheets("NewSheet3").Select

    ActiveSheet.Paste

    Range("D1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Content"

    Range("D2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-3],""|"",RC[-2])"

    Selection.AutoFill Destination:=Range("D2:D8000")

    Range("E2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=StringToBase64(RC[-2])"

    Selection.AutoFill Destination:=Range("E2:E8000")

    Selection.AutoFilter

    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:="<>"

    

    Sheets.Add(After:=ActiveSheet).Name = "NewSheet4"

    Sheets("DATASHEET").Select

    Range("I:I,H:H,A:A").Select

    Range("A1").Activate

    Selection.Copy

    Sheets("NewSheet4").Select

    ActiveSheet.Paste

    Range("D1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Content"

    Range("D2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-3],""|"",RC[-2])"

    Selection.AutoFill Destination:=Range("D2:D8000")

    Range("E2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=StringToBase64(RC[-2])"

    Selection.AutoFill Destination:=Range("E2:E8000")

    Selection.AutoFilter

    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:="<>"

    

    Sheets.Add(After:=ActiveSheet).Name = "NewSheet5"

    Sheets("DATASHEET").Select

    Range("K:K,J:J,A:A").Select

    Range("A1").Activate

    Selection.Copy

    Sheets("NewSheet5").Select

    ActiveSheet.Paste

    Range("D1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Content"

    Range("D2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-3],""|"",RC[-2])"

    Selection.AutoFill Destination:=Range("D2:D8000")

    Range("E2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=StringToBase64(RC[-2])"

    Selection.AutoFill Destination:=Range("E2:E8000")

    Selection.AutoFilter

    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:="<>"

    

    Sheets.Add(After:=ActiveSheet).Name = "NewSheet6"

    Sheets("DATASHEET").Select

    Range("M:M,L:L,A:A").Select

    Range("A1").Activate

    Selection.Copy

    Sheets("NewSheet6").Select

    ActiveSheet.Paste

    Range("D1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "Content"

    Range("D2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-3],""|"",RC[-2])"

    Selection.AutoFill Destination:=Range("D2:D8000")

    Range("E2").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=StringToBase64(RC[-2])"

    Selection.AutoFill Destination:=Range("E2:E8000")

    Selection.AutoFilter

    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:="<>"

    

    Sheets.Add(After:=ActiveSheet).Name = "Main"

    Sheets("Main").Select

    Range("A1").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "Title"

    Range("B1").Select

    ActiveCell.FormulaR1C1 = "Content"

End Sub


Function StringToBase64(str As String) As String

    Dim objXML As Object

    Set objXML = CreateObject("MSXML2.DOMDocument")

    Dim objNode As Object

    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"

    objNode.nodeTypedValue = Stream_StringToBinary(str)

    StringToBase64 = objNode.text

End Function


Function Stream_StringToBinary(text As String) As Variant

    Const adTypeText As Integer = 2

    Const adTypeBinary As Integer = 1

    Dim binaryStream As Object

    Set binaryStream = CreateObject("ADODB.Stream")

    binaryStream.Type = adTypeText

    binaryStream.Charset = "us-ascii"

    binaryStream.Open

    binaryStream.WriteText text

    binaryStream.Position = 0

    binaryStream.Type = adTypeBinary

    Stream_StringToBinary = binaryStream.Read

End Function





Sub Step2()

'

' Macro5 Macro

'


'

    Sheets("NewDATASHEET").Select

    Range("D2:E8000").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Main").Select

    Range("A2").Select

    ActiveSheet.Paste

    Sheets("NewSheet2").Select

    Range("D2:E8000").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Main").Select

    Range("A8002").Select

    ActiveSheet.Paste

    Sheets("NewSheet3").Select

    Range("D2:E8000").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Main").Select

    Range("A16002").Select

    ActiveSheet.Paste

    Sheets("NewSheet4").Select

    Range("D2:E8000").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Main").Select

    Range("A24002").Select

    ActiveSheet.Paste

    Sheets("NewSheet5").Select

    Range("D2:E8000").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Main").Select

    Range("A32002").Select

    ActiveSheet.Paste

    Sheets("NewSheet6").Select

    Range("D2:E8000").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Main").Select

    Range("A40002").Select

    ActiveSheet.Paste

    ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="<>"

End Sub


Comments

Post a Comment

Popular posts from this blog

Salesforce Development Standards - 1

Naming conventions is very important element in developing a code. Naming convention will make code readable and understandable for others to read your code. always try to prefix the datatype to the variable. always a best practice to use camel case.  Below are some salesforce standard naming conventions Example:  String strMyVariable Integer intIndividual Double dblTotal Date datStartDate Map<String, String> mapDataset  List<Account> lstAccounts Set<String> setStrings I will meet you guys on my next blog until then bye  Ganesh Solai known is a drop unknown is an ocean (Note: This blog is for salesforce developer newbie's. I am going talk about salesforce development basic every newbie should learn and understand. ) --------------------------------------------------------------------------------------------------------------------------
Read more