Home / User Contributed Macros / Convert left - right reports (columns) to vertical lines

Convert left - right reports (columns) to vertical lines

http://www.slideshare.net/SalvatorePerseu/how-to-start-controlling-your-business-process-by-mastering-2-control-planning-models


Example zoho-converting left-right data reports to vertically displayed operational lines.

As starting point in excel macro modules with new added data  (e.g. multiplies/ division, Vlookups etc with other datatables) to endpoint divers Pivot functional business analyses possibilities...

More Execution possibilities in the excel macro module as start off from left-right information data for quick standardized business analyses are in reach if no ERP needed or present.

Macro enabled when logged in as user
Sales - Forecast to Operational Planning - https://docs.zoho.com/file/z1g2044a4dd8e38cd43f49ab1d1619a50a386

Layout standard for copy and try-out.
USE AS LAYOUT TO YOUR OWN EXCEL - https://docs.zoho.com/file/z1g20a8114279232d420385c00a342882c2fb

Can try for yourself of course in your own Zoho!  Zoho version start off script below. Good luck!

Kind regards,

Perseu, Salvatore

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Sub MainMacro()
'

Call InsertLines
Call FormulaCopy
Call FormulaPasteValue
Call Copyplanning

End Sub


Sub InsertLines()
'
Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("V:V").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("X:X").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("Z:Z").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AB:AB").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AD:AD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AF:AF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AH:AH").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AJ:AJ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AL:AL").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AN:AN").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AP:AP").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AR:AR").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AT:AT").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AV:AV").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AX:AX").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AZ:AZ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BB:BB").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BD:BD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BF:BF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BH:BH").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BJ:BJ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BL:BL").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BN:BN").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BP:BP").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BR:BR").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BT:BT").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BV:BV").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BX:BX").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("BZ:BZ").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("cB:cB").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("CD:CD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("CF:CF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("CH:CH").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
    End Sub

Sub FormulaCopy()
'
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=+$B2&""'""&O2&""''""&O$1"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P1000"), Type:=xlFillDefault
    Range("P2:P1000").Select
    Selection.Copy
    Range("R2").Select
    ActiveSheet.Paste
    Range("T2").Select
    ActiveSheet.Paste
    Range("V2").Select
    ActiveSheet.Paste
    Range("X2").Select
    ActiveSheet.Paste
    Range("Z2").Select
    ActiveSheet.Paste
    Range("AB2").Select
    ActiveSheet.Paste
    Range("AD2").Select
    ActiveSheet.Paste
    Range("AF2").Select
    ActiveSheet.Paste
    Range("AH2").Select
    ActiveSheet.Paste
    Range("AJ2").Select
    ActiveSheet.Paste
    Range("AL2").Select
    ActiveSheet.Paste
    Range("AN2").Select
    ActiveSheet.Paste
    Range("AP2").Select
    ActiveSheet.Paste
    Range("AR2").Select
    ActiveSheet.Paste    
    Range("AT2").Select
    ActiveSheet.Paste
    Range("AV2").Select
    ActiveSheet.Paste
 Range("AX2").Select
    ActiveSheet.Paste
    Range("AZ2").Select
    ActiveSheet.Paste
    Range("BB2").Select
    ActiveSheet.Paste   
    Range("BD2").Select
    ActiveSheet.Paste
    Range("BF2").Select
    ActiveSheet.Paste
    Range("BH2").Select
    ActiveSheet.Paste
    Range("BJ2").Select
    ActiveSheet.Paste
    Range("BL2").Select
    ActiveSheet.Paste
    Range("BN2").Select
    ActiveSheet.Paste
    Range("BP2").Select
    ActiveSheet.Paste
    Range("BR2").Select
    ActiveSheet.Paste
    Range("BT2").Select
    ActiveSheet.Paste
    Range("BV2").Select
    ActiveSheet.Paste
    Range("BX2").Select
    ActiveSheet.Paste
    Range("BZ2").Select
    ActiveSheet.Paste
    Range("CB2").Select
    ActiveSheet.Paste
    Range("CD2").Select
    ActiveSheet.Paste
    Range("CF2").Select
    ActiveSheet.Paste
    Range("CH2").Select
    ActiveSheet.Paste
    Range("CJ2").Select  
    ActiveSheet.Paste
 
    End Sub

Sub FormulaPasteValue()
'
    Range("O2:CJ1000").Select 
    Range("O2:CJ1000").formula = Range("O2:CJ1000").value
 
    end sub
 

Sub Copyplanning()
  '  
    Range("P2:P1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("R2:R1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1000").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("T2:T1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1999").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("V2:V1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A2998").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("X2:X1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A3997").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("Z2:Z1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A4996").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AB2:AB1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A5995").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AD2:AD1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A6994").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AF2:AF1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A7993").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AH2:AH1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A8992").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AJ2:AJ1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A9991").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AL2:AL1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A10990").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AN2:AN1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A11989").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AP2:AP1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A12988").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AR2:AR1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A13987").Select
    ActiveSheet.Paste    
    Sheets("Sheet1").Select
    Range("AT2:AT1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A14986").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AV2:AV1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A15985").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AX2:AX1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A16984").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("AZ2:AZ1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A17983").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BB2:BB1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A18982").Select
    ActiveSheet.Paste   
    Sheets("Sheet1").Select
    Range("BD2:BD1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A19981").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BF2:BF1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A20980").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BH2:BH1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A21979").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BJ2:BJ1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A22978").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BL2:BL1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A23977").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BN2:BN1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A24976").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BP2:BP1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A25975").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BR2:BR1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A26974").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BT2:BT1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A27973").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BV2:BV1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A28972").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BX2:BX1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A29971").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("BZ2:BZ1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A30970").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("CB2:CB1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A31969").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("CD2:CD1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A32968").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("CF2:CF1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A33967").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("CH2:CH1000").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A34966").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("CJ2:CJ1000").Select  
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Range("A35965").Select
    ActiveSheet.Paste

    End Sub


Note: This is the Zoho version and thus different to the original Excel code. (no 100% copy paste possible).
Excel codes as starting point will bring you much further. If needed help, contact is possible through PM. (s_perseu@zoho.com)



    		 RSS of this page