PDA

View Full Version : Alignment of new data and add boarders



keilah
05-21-2008, 02:01 AM
see attached workbook EE-modified-9j.xls

Hi

having problems

Need to fix the boarder problem and alignment of the new inserted data with the rest of the worksheet......

See worksheet "Allocation (Base), (Vol) and (Sc.1) , (Sc.2) and (Sc.3)

row 176 on wards all out of line and the rows should be moving down to accomdated the addtion of new data.......whlist keeping its presentation...

question also on EE..http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23419976.html

keilah
05-21-2008, 02:07 AM
ok i forgot to mention that this is the vba code that requires amendment in to order to accomdated the step mentioned....

Sub ads()
Dim STRTROW As Long
Sheets("Allocation (Base)").Visible = True
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Deal Selection").Select
Range("i9:i" & Cells(65536, "i").End(xlUp).Row).Copy
Sheets("Allocation (Base)").Select
STRTROW = Cells(65336, "c").End(xlUp).Row + 1
Range("C" & STRTROW).Select
Selection.PasteSpecial Paste:=xlPasteValues
ENDROW = Cells(65336, "c").End(xlUp).Row
Sheets("Deal Selection").Select
z = Range("B" & Cells(65536, "B").End(xlUp).Row).Value
Sheets("Allocation (Base)").Select
Range("B" & STRTROW & ":B" & ENDROW).Select
Selection.Value = z
Range("d" & STRTROW & ":d" & ENDROW).Select
Selection.Value = Range("D" & STRTROW - 1).Value
Sheets("Deal Selection").Select
Range("B1").Select
Application.CutCopyMode = False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

keilah
05-21-2008, 02:14 AM
And just another point this code runs of the Add Supplier macro button

keilah
05-21-2008, 06:48 AM
would the following work to add boarders around the new inserted data

With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With