Borders

[Alan]

Hi,
I have a file with three Pivot Tables which change size and shape via a
Macro which changes the data in the source sheets and then refreshes the
tables.
I have removed grid lines from the Pivot Table worksheets, however I want
gridlines in the body of the Pivot Table.
I'm using this at present

Sub Pivot1 ()
Sheet1.Range([A4]).Select 'A4 is a formula to get the last cell in the
Pivot Table
Border
End Sub

Function Border()
With Selection.Borders(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlThin
..Weight = xlThin
End With
End Sub

Function Border is obviously abbreviated, (xlEdgeRight) etc

Can this be done without having to actually select the range and therefore
the sheet to run the code to put in the border?
I would like to be able to use something like

Sheet1.Range([A4:G34]).Borders(xlEdgeLeft).LineStyle = xlContinuous.Weight =
xlThin.Weight = xlThin
(I know that doesn't work, lol)

I dont want to have to select any range or sheet if I can avoid it

Any help gratefully recieved,
Regards,
Alan.

 

[Alan]

Should be
Sheet1.Range([A4]).Borders(xlEdgeLeft).LineStyle = xlContinuous.Weight =
xlThin.Weight = xlThin
Regards,

 

[Dave Peterson]

You can work directly with the range like:

Function DoBorder()
With worksheets("sheet1").range("A4:G34").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Weight = xlThin
End With
End Sub

I wouldn't use Border as the name of the function. It's an object/property in
VBA.

Hi,
I have a file with three Pivot Tables which change size and shape via a
Macro which changes the data in the source sheets and then refreshes the
tables.
I have removed grid lines from the Pivot Table worksheets, however I want
gridlines in the body of the Pivot Table.
I'm using this at present

Sub Pivot1 ()
Sheet1.Range([A4]).Select 'A4 is a formula to get the last cell in the
Pivot Table
Border
End Sub

Function Border()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Weight = xlThin
End With
End Sub

Function Border is obviously abbreviated, (xlEdgeRight) etc

Can this be done without having to actually select the range and therefore
the sheet to run the code to put in the border?
I would like to be able to use something like

Sheet1.Range([A4:G34]).Borders(xlEdgeLeft).LineStyle = xlContinuous.Weight =
xlThin.Weight = xlThin
(I know that doesn't work, lol)

I dont want to have to select any range or sheet if I can avoid it

Any help gratefully recieved,
Regards,
Alan.

 

[Alan]

Thanks Dave,
Regards,
Alan.

You can work directly with the range like:

Function DoBorder()
With worksheets("sheet1").range("A4:G34").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Weight = xlThin
End With
End Sub

I wouldn't use Border as the name of the function. It's an
object/property in
VBA.

Hi,
I have a file with three Pivot Tables which change size and shape via a
Macro which changes the data in the source sheets and then refreshes the
tables.
I have removed grid lines from the Pivot Table worksheets, however I want
gridlines in the body of the Pivot Table.
I'm using this at present

Sub Pivot1 ()
Sheet1.Range([A4]).Select 'A4 is a formula to get the last cell in the
Pivot Table
Border
End Sub

Function Border()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Weight = xlThin
End With
End Sub

Function Border is obviously abbreviated, (xlEdgeRight) etc

Can this be done without having to actually select the range and
therefore
the sheet to run the code to put in the border?
I would like to be able to use something like

Sheet1.Range([A4:G34]).Borders(xlEdgeLeft).LineStyle =
xlContinuous.Weight =
xlThin.Weight = xlThin
(I know that doesn't work, lol)

I dont want to have to select any range or sheet if I can avoid it

Any help gratefully recieved,
Regards,
Alan.