Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 7th 08, 05:13 AM posted to microsoft.public.excel.newusers
DEE DEE is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 250
Default 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   Report Post  
Old November 7th 08, 11:18 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default 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   Report Post  
Old November 7th 08, 06:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find if value exits in array Glynn Excel Worksheet Functions 6 September 5th 08 08:46 PM
When saving, Excel prompt me for all worksheet passwords Tzardos Excel Worksheet Functions 1 July 10th 08 12:53 AM
Closing a workbook, exits Excel 2007 too Sonya Torian Excel Discussion (Misc queries) 0 March 12th 08 03:37 PM
Closing a workbook exits Excel Application DarS Excel Discussion (Misc queries) 2 September 27th 07 01:51 PM
Excel Exits on File Close with Outlook Erin Searfoss Excel Discussion (Misc queries) 1 January 16th 05 10:43 PM


All times are GMT +1. The time now is 04:32 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017