Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Skip or Ignore Missing Worksheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Skip or Ignore Missing Worksheets
I have an Excel workbook that allows a user to select the number of
worksheets they want to create (it's a makeshift Purchase Order - one
worksheet equals one style).

Because there can be a large number of worksheets, I've only put the
formulas in the first (pre-existing) worksheet. I want to create a
"calculate entire Purchase Order" macro that copies the formulas from
the first (pre-existing) worksheet and pastes it into however many
worksheets that have the name "Style XXX Key". The 'XXX' is the
variable that can be anywhere from 1 to 12.

Here's the code I have (I have the sheets hidden until they click the
"Calculate" button:

Sub CalcPO()
'
' CalcPO Macro
' Macro recorded 10/22/2007 by xyou
'

'
On Error Resume Next
Sheets("Style 1 Key").Visible = True
Sheets("Style 2 Key").Visible = True
Sheets("Style 3 Key").Visible = True
Sheets("Style 4 Key").Visible = True
Sheets("Style 5 Key").Visible = True
Sheets("Style 6 Key").Visible = True
Sheets("Style 7 Key").Visible = True
Sheets("Style 8 Key").Visible = True
Sheets("Style 9 Key").Visible = True
Sheets("Style 10 Key").Visible = True
Sheets("Style 11 Key").Visible = True
Sheets("Style 12 Key").Visible = True
On Error GoTo 0
Sheets("Style 1 Key").Select
Cells.Select
Selection.Copy
Sheets("Style 2 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 2",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
Sheets("Style 3 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 3",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 4 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 4",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 5 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 5",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 6 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 6",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 7 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 7",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 8 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 8",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 9 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 9",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 10 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 10",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 11 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 11",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 12 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 12",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
Sheets("Style 1 Key").Select
Application.CutCopyMode = False
Range("B1").Select
End Sub


It works great until it gets to the "Style XXX Key" worksheet that
doesn't exists - then it prompts me to select a file from my PC.
Yikes!

Any help would be most appreciated.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default Skip or Ignore Missing Worksheets
Yikes! is right ;-)

Try the version below.

HTH,
Bernie
MS Excel MVP

Sub CalcPOVer2()
Dim i As Integer
Dim myF As Range
Dim myA As Range
Dim mySht As Worksheet

On Error Resume Next

For i = 1 To 12
Sheets("Style " & i & " Key").Visible = True
Next i

Set myF = Sheets("Style 1 Key").Cells.SpecialCells(xlCellTypeFormulas)

For i = 2 To 12
On Error GoTo NotFound:
Set mySht = Sheets("Style " & i & " Key")
For Each myA In myF.Areas
myA.Copy mySht.Range(myA.Address)
Next myA
mySht.Cells.Replace What:="Style 1", Replacement:="Style " & i, LookAt:=xlPart
NotFound:
Resume StartAgain
StartAgain:
Next i

Application.CutCopyMode = False
End Sub


"abba92" wrote in message
oups.com...
I have an Excel workbook that allows a user to select the number of
worksheets they want to create (it's a makeshift Purchase Order - one
worksheet equals one style).

Because there can be a large number of worksheets, I've only put the
formulas in the first (pre-existing) worksheet. I want to create a
"calculate entire Purchase Order" macro that copies the formulas from
the first (pre-existing) worksheet and pastes it into however many
worksheets that have the name "Style XXX Key". The 'XXX' is the
variable that can be anywhere from 1 to 12.

Here's the code I have (I have the sheets hidden until they click the
"Calculate" button:

Sub CalcPO()
'
' CalcPO Macro
' Macro recorded 10/22/2007 by xyou
'

'
On Error Resume Next
Sheets("Style 1 Key").Visible = True
Sheets("Style 2 Key").Visible = True
Sheets("Style 3 Key").Visible = True
Sheets("Style 4 Key").Visible = True
Sheets("Style 5 Key").Visible = True
Sheets("Style 6 Key").Visible = True
Sheets("Style 7 Key").Visible = True
Sheets("Style 8 Key").Visible = True
Sheets("Style 9 Key").Visible = True
Sheets("Style 10 Key").Visible = True
Sheets("Style 11 Key").Visible = True
Sheets("Style 12 Key").Visible = True
On Error GoTo 0
Sheets("Style 1 Key").Select
Cells.Select
Selection.Copy
Sheets("Style 2 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 2",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
Sheets("Style 3 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 3",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 4 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 4",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 5 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 5",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 6 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 6",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 7 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 7",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 8 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 8",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 9 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 9",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 10 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 10",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 11 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 11",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Style 12 Key").Select
Cells.Select
ActiveSheet.Paste
Selection.Replace What:="Style 1", Replacement:="Style 12",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
Sheets("Style 1 Key").Select
Application.CutCopyMode = False
Range("B1").Select
End Sub


It works great until it gets to the "Style XXX Key" worksheet that
doesn't exists - then it prompts me to select a file from my PC.
Yikes!

Any help would be most appreciated.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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 missing numbers in list, ignore duplicates Galceran Excel Discussion (Misc queries) 0 October 30th 07 05:05 PM
Ignore Missing Worksheets Jim Excel Programming 2 October 20th 07 09:10 AM
ignore missing values while ploting graph Excel plot Excel Discussion (Misc queries) 2 September 15th 05 06:48 PM
On Error? Creates 1 missing worksheet then never detects any other missing worksheets Craigm[_35_] Excel Programming 2 August 1st 05 02:39 PM
how can i get the slope function to ignore missing data? Delmar Excel Discussion (Misc queries) 0 December 2nd 04 05:55 PM


All times are GMT +1. The time now is 03:58 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement