Solved: Amend vba code to apply same font and styling

[keilah]

Hi People

this is my first crack at some code but i have gone wrong or miss something out......

i have also attached a copy of the work book......

what the code is missing is how do i make the font and placement (centre) of column O exactly the same as column J....

here is the code

Sub copy_allo()
Application.ScreenUpdating = False
Sheets("Tables").Select
Range("O5:O" & Sheets("Tables").Cells(65536, "O").End(xlUp).Row).ClearContents
Sheets("Allocation (Base)").Select
Range("E6:E" & Sheets("Allocation (Base)").Cells(65536, "E").End(xlUp).Row).Copy
Sheets("Tables").Select
Range("o5").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("J5").Copy
Range("J5:J" & Sheets("Tables").Cells(65536, "J").End(xlUp).Row).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Range("j5").Select
Sheets("Deal Selection").Select
Range("b1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Column O already looks like column J to me, and your example workbook doesn't have sheets with those names. Try again?

[keilah]

look from point O31 onwards.....apologies.

[keilah]

the example workbook.......is one section of the total workbook file is to big to upload

[Bob Phillips]

Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row

With .Range("O5:O" & LastRow)
.HorizontalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Font.ColorIndex = 15
End With

[keilah]

thanks for the feedback......

[Bob Phillips]

thanks for the feedback......

???