"What If" in a macro

Andy Pilkington

Board Regular
Joined
Jan 23, 2014
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Hi, I have a macro that moves a worksheet from the current workbook into a new workbook therefore the worksheet no longer exists in the current workbook. Of course when I try to execute the same macro again it goes into error because the sheet is no longer there.
Is there a way I can avoid this by, for example, including a "what-if" that bypasses the command or executes the next command in the macro?
Thanks in advance.
AP
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You could use something like
VBA Code:
If Evaluate("isref('Sheet 1'!A1)") Then
   'do something
End If
to check if the sheet exists
 
Upvote 0
Unfortunately I am no expert with VBA. My macro looks like this at the moment ...

Sub GoToVal1()
'
' GoToVal1 Macro
'

'
Application.ScreenUpdating = False
Sheets("Int Valn (1)").Visible = True
Sheets("Int Valn (1)").Select
Calculate
Sheets("Int Valn (1)").Select
Sheets("Int Valn (1)").Move
Application.ScreenUpdating = True
End Sub

But when the "Int Valn (1)" sheet has already been moved I would like the above macro to invoke another macro instead. Is this possible?
 
Upvote 0
In that case you could use
VBA Code:
If Evaluate("isref('Int Valn (1)'!A1)") Then
Call Macro1
End If
Just change Macro1 to the name of you macro.
 
Upvote 0
In that case you could use
VBA Code:
If Evaluate("isref('Int Valn (1)'!A1)") Then
Call Macro1
End If
Just change Macro1 to the name of you macro.

Seems to accept the new input but doesn't execute the GoToBuildUp macro and still wants to progress with the rest of the macro. It trips up when the original command wants to open the now missing worksheet, see below in bold red ...

Sub GoToVal1()
'
' GoToVal1 Macro
'

'
Application.ScreenUpdating = False
If Evaluate("isref('Int Valn (1)'!A1)") Then
Call GoToBuildUp
End If
Sheets("Int Valn (1)").Visible = True
Sheets("Int Valn (1)").Select
Calculate
Sheets("Int Valn (1)").Select
Sheets("Int Valn (1)").Move
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Put an Exit Sub line after the Call
 
Upvote 0
Put an Exit Sub line after the Call

I changed the macro to read ...

Sub GoToVal1()
'
' GoToVal1 Macro
'

'
Application.ScreenUpdating = False
If Evaluate("isref('Int Valn (1)'!A1)") Then
Call GoToBuildUp
Exit Sub
End If
Sheets("Int Valn (1)").Visible = True
Sheets("Int Valn (1)").Select
Calculate
Sheets("Int Valn (1)").Select
Sheets("Int Valn (1)").Move
Application.ScreenUpdating = True
End Sub

... but it still went further and stalled at the same place.
 
Upvote 0
Oops, missing a very little word. It should be
VBA Code:
If Not Evaluate("isref('Int Valn (1)'!A1)") Then
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top