Prompt msg if the worksheet already exits
 

Hi,

Below was my recorded macro . What shall i do to prompt user with a msg when
both worksheets "Shop 1" & "Shop 1 Pivot" already exits when click onto the
button
with the assign macro ??

Thanks in advance !!!

********************************************
Worksheets("Pivot Table (Parts)").Activate
For i = 26 To 60
If Cells(i, "u") = "Shop 1" Then
Cells(i, "v").Select
Selection.ShowDetail = True
Exit For
End If
Next i
ActiveSheet.Select
ActiveSheet.Name = "Shop 1"
Range("D25").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Shop 1'!R1C1:R200C39").CreatePivotTable TableDestination:="",TableName _
:="PivotTable274", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable274").AddFields RowFields:="Kit", _
ColumnFields:="MONTH"
ActiveSheet.PivotTables("PivotTable274").PivotFiel ds("Kit"). _
Orientation = xlDataField
ActiveSheet.PivotTables("PivotTable274").ColumnGra nd = False
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable274").PivotFiel ds("Kit")
.PivotItems("(blank)").Visible = False
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
ActiveSheet.Select
ActiveSheet.Name = "Shop 1 Pivot"
Range("C25").Select
Sheets("Shop 1 Pivot").Select

End Sub

Prompt msg if the worksheet already exits
 

Sub dural()
For Each sh In Worksheets
If sh.Name = "Shop 1" Or sh.Name = "Shop 1 Pivot" Then
MsgBox (sh.Name & " already exists")
End If
Next
End Sub
--
Gary''s Student - gsnu200812

Prompt msg if the worksheet already exits
 

Hi,

I think you need to modify the previous suggestion to be AND not OR, if I
understand your question correctly.
--
Thanks,
Shane Devenshire


"Dee" wrote:

Hi,

Below was my recorded macro . What shall i do to prompt user with a msg when
both worksheets "Shop 1" & "Shop 1 Pivot" already exits when click onto the
button
with the assign macro ??

Thanks in advance !!!

********************************************
Worksheets("Pivot Table (Parts)").Activate
For i = 26 To 60
If Cells(i, "u") = "Shop 1" Then
Cells(i, "v").Select
Selection.ShowDetail = True
Exit For
End If
Next i
ActiveSheet.Select
ActiveSheet.Name = "Shop 1"
Range("D25").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Shop 1'!R1C1:R200C39").CreatePivotTable TableDestination:="",TableName _
:="PivotTable274", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable274").AddFields RowFields:="Kit", _
ColumnFields:="MONTH"
ActiveSheet.PivotTables("PivotTable274").PivotFiel ds("Kit"). _
Orientation = xlDataField
ActiveSheet.PivotTables("PivotTable274").ColumnGra nd = False
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable274").PivotFiel ds("Kit")
.PivotItems("(blank)").Visible = False
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
ActiveSheet.Select
ActiveSheet.Name = "Shop 1 Pivot"
Range("C25").Select
Sheets("Shop 1 Pivot").Select

End Sub