Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find if value exits in array | Excel Worksheet Functions | |||
When saving, Excel prompt me for all worksheet passwords | Excel Worksheet Functions | |||
Closing a workbook, exits Excel 2007 too | Excel Discussion (Misc queries) | |||
Closing a workbook exits Excel Application | Excel Discussion (Misc queries) | |||
Excel Exits on File Close with Outlook | Excel Discussion (Misc queries) |