Limiting a macro to run on a SPECIFIC sheet ina Workbook

Colobri

New Member
Joined
Feb 18, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
  2. Web
Hi! I have a workbook with 3 worksheets in it. the 3rd sheet, called PRACTICEVERSION is just that, a sheet to practice the steps of the course. FYI, sheet 1 is the actua "form" that users will keep their records on. Sheet 2 is all text and gives them the Directions as to what to put in each cell. Sheet 3 (PracticeVersion) is an exact copy of the "Master", ie Sheet 1. If youre going to practice, make mistakes, etc., do so on the PracticeVersion. I wrote a macro to clear/delete any/all "student-input" data while retaining the formulas. But, unfortunately, if they happen to execute the macro on the REAL, MASTER sheet (Sheet 1) it deletes all of the data on THAT sheet, too! NOT a god thing! So, my question is, on the Macro below, how do I SPECIFY that executing the macro ONLY works if you are on the PRACTICEVERSION sheet and NOT if you are on the Master, REAL sheet?

Sub ClearingWF_PracticeVersion()
'
' ClearingWF_PracticeVersion Macro
'


Range("B2:B3").Select
Selection.ClearContents
Range("D15:D16").Select
Selection.ClearContents
Range("E15:E16").Select
Selection.ClearContents
Range("E18").Select
Selection.ClearContents
Range("L15:L16").Select
Selection.ClearContents
Range("L19:L20").Select
Selection.ClearContents
Range("M15:M16").Select
Selection.ClearContents
ActiveWorkbook.Save
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B2").Select
ActiveWorkbook.Save
End Sub

Thank you!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
VBA Code:
Sheets("Sheet1").select

Put this on top of your code and replace "Sheet1" with whichever sheet you want it to run on.
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
Sub ClearingWF_PracticeVersion()
   Sheets("PracticeVersion").Range("B2:B3,D15:E16,E18,L15:L16,L19:L20,M15:M16").ClearContents
   ActiveWorkbook.Save
End Sub
 
Upvote 0
Thanks for the responses. Jambi, I did as you said but it didn't work. It STILL deleted the data input into Sheet 1 , not a good thing. Furthermore, when I went back in to Step Into the macro, I received an error msg of Can't Execute code in break mode. When I choose OK it closes the window and takes back to my Excel worksheet,
 
Upvote 0
Mr Excel: Tried that, still wipes out my Sheet1, Master Real Deal sheet data. :(
 
Upvote 0
That code I posted only clears data on the PracticeVersion sheet. If cells are being cleared on any other sheet, something else is going on.
Check that you don't have any other code running.
 
Upvote 0
MrExcel: Thank you! I have tried all KINDS of syntax to figure it out. I DO have only one other macro related to this that I use on a daily basis. It looks like this: (Note continued at bottom)...

Sub WF_DailyUpdate()
'
' WF_DailyUpdate Macro
'

'
Range("E15:E16").Select
Selection.Copy
Range("L15").Select
ActiveSheet.Paste
Range("L23").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("L19:L20").Select
Selection.Copy
Range("E15:E16").Select
ActiveSheet.Paste
Range("M15:M16").Select
ActiveSheet.Paste
Range("M19").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

This shouldn't mess with a totally different macro, I wouldn't think.

Thoughts?
 
Upvote 0
No that won't affect anything.
How are the macros called?
Also do you have any formulae on the Master sheet looking at the Practice sheet?
 
Upvote 0
MrExcel: I'm not sure I understand the "How are the macros called?" question. The macro is called/named RefreshPracticeVersion. When I recorded the macro, I filled in the cells with fictitious numbers, saved the document, then created a New Macro, recorded is by deleting the previously input numbers, saved the document, then Stopped Recording. TO execute the macro, I placed an Icon up on the toolbar, the/my theory being, after filling in and practicing, should I want to review and practice again, starting with a new example, I merely go to the Practice Version sheet, click on the icon on the toolbar, the data is deleted, and I can re-use/type in new info.

To create the Practice Version, I took the Master Sheet, Select All > Copy > Open a new sheet in the workbook > Name the new sheet PRACTICE VERSION > Paste > Save. There is no instance where any data being typed into the Practice Version is taken/copied/linked from the Master Sheet or vice versa.
 
Upvote 0
What macro is being called by the icon on the toolbar?
Also did you delete your code & replace it with mine?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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