Borders
 

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.

Borders
 

Should be
Sheet1.Range([A4]).Borders(xlEdgeLeft).LineStyle = xlContinuous.Weight =
xlThin.Weight = xlThin
Regards,
"Alan" wrote in message
...
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.

Borders
 

You can work directly with the range like:

Function DoBorder()
With worksheets("sheet1").range("A4:G34").Borders(xlEdg eLeft)
.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.


Alan wrote:

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.

--

Dave Peterson

Borders
 

Thanks Dave,
Regards,
Alan.
"Dave Peterson" wrote in message
...
You can work directly with the range like:

Function DoBorder()
With worksheets("sheet1").range("A4:G34").Borders(xlEdg eLeft)
.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.


Alan wrote:

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.

--

Dave Peterson