Pivot Table NewSheet Event Hide Columns
 

OK here is what I'm trying to do... I have a pivot table and when I
drill down into it I want specific cell names to hide it's column when
the new sheet pops up. I have the code to just be able to do this
with an active sheet, but I can't seem to adopt this to a new sheet
event. And to make things a little more difficult, I already have one
new sheet event for formatting. Anyway, here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
If UCase(Range("B1").Value) = "YES" Then
Columns(2).Hidden = True
Else
Columns(2).Hidden = False
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Columns("B:B").NumberFormat = "$#,###"
Rows("1:1").Select
Selection.Font.ColorIndex = 37
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 55
Range("F5").Select
End Sub

Thanks

Tyson

Pivot Table NewSheet Event Hide Columns
 

I have figured out a little more to the puzzle but I still can't get
exactly what I want. Here is the formula as it stands now:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Columns("B:B").NumberFormat = "$#,###"
Rows("1:1").Select
Selection.Font.ColorIndex = 37
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 55
Cells.Select
Range("P11").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("b1")) Is Nothing Then
If UCase(Range("b1").Value) = "HIDE" Then
Columns(2).Hidden = True
Else
Columns(2).Hidden = False
End If
End If
Application.EnableEvents = True
End Sub

PROBLEMS:

I can't figure out how to use a wildcard in this. I would like it to
be "HIDE*", but it takes the * as part of the text, and not as a
wildcard.

PROBLEM #2

How do I make the columns with the word "Hide" in the top cell hide?
Right now it is just column 2... but I would like if column "T" had
"hide*" for it to hide column 20. Make sense?

Any help would be great.


Thanks

Tyson

(Tysone) wrote in message om...
OK here is what I'm trying to do... I have a pivot table and when I
drill down into it I want specific cell names to hide it's column when
the new sheet pops up. I have the code to just be able to do this
with an active sheet, but I can't seem to adopt this to a new sheet
event. And to make things a little more difficult, I already have one
new sheet event for formatting. Anyway, here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
If UCase(Range("B1").Value) = "YES" Then
Columns(2).Hidden = True
Else
Columns(2).Hidden = False
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Columns("B:B").NumberFormat = "$#,###"
Rows("1:1").Select
Selection.Font.ColorIndex = 37
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 55
Range("F5").Select
End Sub

Thanks

Tyson

Pivot Table NewSheet Event Hide Columns
 

You could just look at the first 4 characters:

If UCase(Range("b1").Value) = "HIDE" Then
if ucase(left(range("B1").value,4)) = "HIDE" then


dim iCol as long
for icol = me.usedrange.columns(me.usedrange.columns.count).c olumn to 1 step -1
if ucase(left(cells(1,icol).value,4)) = "HIDE" then
columns(icol).hidden = true
else
columns(icol).hidden = false
end if
next icol

might work ok.

Tysone wrote:

I have figured out a little more to the puzzle but I still can't get
exactly what I want. Here is the formula as it stands now:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Columns("B:B").NumberFormat = "$#,###"
Rows("1:1").Select
Selection.Font.ColorIndex = 37
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 55
Cells.Select
Range("P11").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("b1")) Is Nothing Then
If UCase(Range("b1").Value) = "HIDE" Then
Columns(2).Hidden = True
Else
Columns(2).Hidden = False
End If
End If
Application.EnableEvents = True
End Sub

PROBLEMS:

I can't figure out how to use a wildcard in this. I would like it to
be "HIDE*", but it takes the * as part of the text, and not as a
wildcard.

PROBLEM #2

How do I make the columns with the word "Hide" in the top cell hide?
Right now it is just column 2... but I would like if column "T" had
"hide*" for it to hide column 20. Make sense?

Any help would be great.

Thanks

Tyson

(Tysone) wrote in message om...
OK here is what I'm trying to do... I have a pivot table and when I
drill down into it I want specific cell names to hide it's column when
the new sheet pops up. I have the code to just be able to do this
with an active sheet, but I can't seem to adopt this to a new sheet
event. And to make things a little more difficult, I already have one
new sheet event for formatting. Anyway, here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
If UCase(Range("B1").Value) = "YES" Then
Columns(2).Hidden = True
Else
Columns(2).Hidden = False
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Columns("B:B").NumberFormat = "$#,###"
Rows("1:1").Select
Selection.Font.ColorIndex = 37
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 55
Range("F5").Select
End Sub

Thanks

Tyson

--

Dave Peterson