Newbie attempting VBA Scripting
Well heres the macro i coded, it works like a charm, but its really
really messy im sure. Any suggestions for cleaning it up/ making it
less specific to THIS EXACT Layout and file etc. Like can i have it
look for certain column names and then figure out their position,
instead of just assigning their position and having the macro assume
they are there and error if they arent. Anyway, here it is. Thanks
for your help Joel.
Sub AutoJV()
'///Create New Sheet "Prem Comm JV" and Format
It/////////////////////////////////
Worksheets.Add after:=Sheets(Sheets.Count)
Set NewSheet = ActiveSheet
ActiveSheet.Name = "Prem Comm JV"
Range("B1").FormulaR1C1 = "Unit"
Range("C1").FormulaR1C1 = "Ledger"
Range("D1").FormulaR1C1 = "Account"
Range("E1").FormulaR1C1 = "Alt Account"
Range("F1").FormulaR1C1 = "Oper Unit"
Range("G1").FormulaR1C1 = "Dept ID"
Range("H1").FormulaR1C1 = "Currency"
Range("I1").FormulaR1C1 = "Amount"
Range("J1").FormulaR1C1 = "N/R"
Range("K1").FormulaR1C1 = "Rate Type"
Range("L1").FormulaR1C1 = "Rate"
Range("M1").FormulaR1C1 = "Base Amount"
Range("N1").FormulaR1C1 = "Stat"
Range("O1").FormulaR1C1 = "Stat Amount"
Range("P1").FormulaR1C1 = "Product"
Range("Q1").FormulaR1C1 = "Year"
Range("R1").FormulaR1C1 = "Mngt Code"
Range("S1").FormulaR1C1 = "Risk Loc"
Range("T1").FormulaR1C1 = "Function"
Range("U1").FormulaR1C1 = "Project"
Range("V1").FormulaR1C1 = "Affiliate"
Range("B1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Font.Bold = True
Selection.Copy
Range("C1:V1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:V").EntireColumn.AutoFit
'/////////////////////////////////////////////////////////
'///The Fun Stuff////////////////////////////////////////////////////
OldRowCounter = 2
NewRowCounter = 2
With Worksheets("Premiums Commissions")
Do While .Range("A" & OldRowCounter) < ""
UnitCol = .Range("D" & OldRowCounter)
With NewSheet
.Range("A" & NewRowCounter) = "Agents Bal - Affiliate
Assumed"
.Range("A" & (NewRowCounter + 1)) = "AWP - Affiliate"
.Range("A" & (NewRowCounter + 2)) = "Asmd Cont Comm Res-
Affil"
.Range("A" & (NewRowCounter + 3)) = "Asmd Comm-Affiliate"
.Range("B" & NewRowCounter) = UnitCol
.Range("B" & (NewRowCounter + 1)) = UnitCol
.Range("B" & (NewRowCounter + 2)) = UnitCol
.Range("B" & (NewRowCounter + 3)) = UnitCol
.Range("C" & NewRowCounter) = "CORE"
.Range("C" & (NewRowCounter + 1)) = "CORE"
.Range("C" & (NewRowCounter + 2)) = "CORE"
.Range("C" & (NewRowCounter + 3)) = "CORE"
.Range("D" & NewRowCounter) = "1270220076"
.Range("D" & (NewRowCounter + 1)) = "3010620076"
.Range("D" & (NewRowCounter + 2)) = "2144120076"
.Range("D" & (NewRowCounter + 3)) = "6010620076"
.Range("G" & NewRowCounter) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)"
.Range("G" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)"
.Range("G" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)"
.Range("G" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Dept Lookup'!A2:B65536,2,FALSE)"
.Range("H" & NewRowCounter) = "=+'Premiums Commissions'!I"
& OldRowCounter & ""
.Range("H" & (NewRowCounter + 1)) = "=+'Premiums
Commissions'!G" & OldRowCounter & ""
.Range("H" & (NewRowCounter + 2)) = "=+'Premiums
Commissions'!I" & OldRowCounter & ""
.Range("H" & (NewRowCounter + 3)) = "=+'Premiums
Commissions'!G" & OldRowCounter & ""
.Range("I" & NewRowCounter) = "=+'Premiums Commissions'!J"
& OldRowCounter & ""
.Range("I" & NewRowCounter).NumberFormat = "#,##0.00_);
[Red](#,##0.00)"
.Range("I" & (NewRowCounter + 1)) = "=-'Premiums
Commissions'!H" & OldRowCounter & ""
.Range("I" & (NewRowCounter + 1)).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
.Range("I" & (NewRowCounter + 2)) = "=+'Premiums
Commissions'!N" & OldRowCounter & ""
.Range("I" & (NewRowCounter + 2)).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
.Range("I" & (NewRowCounter + 3)) = "=-'Premiums
Commissions'!L" & OldRowCounter & ""
.Range("I" & (NewRowCounter + 3)).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
.Range("M" & NewRowCounter) = "=+'Premiums Commissions'!K"
& OldRowCounter & ""
.Range("M" & NewRowCounter).NumberFormat = "#,##0.00_);
[Red](#,##0.00)"
.Range("M" & (NewRowCounter + 1)) = "=-'Premiums
Commissions'!K" & OldRowCounter & ""
.Range("M" & (NewRowCounter + 1)).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
.Range("M" & (NewRowCounter + 2)) = "=+'Premiums
Commissions'!M" & OldRowCounter & ""
.Range("M" & (NewRowCounter + 2)).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
.Range("M" & (NewRowCounter + 3)) = "=-'Premiums
Commissions'!M" & OldRowCounter & ""
.Range("M" & (NewRowCounter + 2)).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
.Range("P" & NewRowCounter) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Product Lookup'!
A2:B65536,2,FALSE)"
.Range("P" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Product Lookup'!
A2:B65536,2,FALSE)"
.Range("P" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Product Lookup'!
A2:B65536,2,FALSE)"
.Range("P" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Product Lookup'!
A2:B65536,2,FALSE)"
.Range("R" & NewRowCounter) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)"
.Range("R" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)"
.Range("R" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)"
.Range("R" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'MCC Lookup'!A2:B65536,2,FALSE)"
.Range("S" & NewRowCounter) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'!
A2:B65536,2,FALSE)"
.Range("S" & (NewRowCounter + 1)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'!
A2:B65536,2,FALSE)"
.Range("S" & (NewRowCounter + 2)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'!
A2:B65536,2,FALSE)"
.Range("S" & (NewRowCounter + 3)) = "=VLOOKUP('Premiums
Commissions'!F" & OldRowCounter & ",'Risk Loc Lookup'!
A2:B65536,2,FALSE)"
.Range("V" & NewRowCounter) = "=+'Premiums Commissions'!B"
& OldRowCounter & ""
.Range("V" & (NewRowCounter + 1)) = "=+'Premiums
Commissions'!B" & OldRowCounter & ""
.Range("V" & (NewRowCounter + 2)) = "=+'Premiums
Commissions'!B" & OldRowCounter & ""
.Range("V" & (NewRowCounter + 3)) = "=+'Premiums
Commissions'!B" & OldRowCounter & ""
'=+'Premiums Commissions'!$B$2
NewRowCounter = NewRowCounter + 5
End With
OldRowCounter = OldRowCounter + 1
Loop
End With
'/////////////////////////////////////////////////////////
'////////////Grand
Finale//////////////////////////////////////////////
Columns("A:V").EntireColumn.AutoFit
'/////////////////////////////////////////////////////////
End Sub
|