Limiting a macro to run on a SPECIFIC sheet ina Workbook

Colobri

New Member
Joined
Feb 18, 2020
Messages
7
Office Version
2013
Platform
Windows, 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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Jambi46n2

Board Regular
Joined
May 24, 2016
Messages
243
Office Version
365, 2019
Platform
Windows
VBA Code:
Sheets("Sheet1").select
Put this on top of your code and replace "Sheet1" with whichever sheet you want it to run on.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
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
 

Colobri

New Member
Joined
Feb 18, 2020
Messages
7
Office Version
2013
Platform
Windows, Web
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,
 

Colobri

New Member
Joined
Feb 18, 2020
Messages
7
Office Version
2013
Platform
Windows, Web
Mr Excel: Tried that, still wipes out my Sheet1, Master Real Deal sheet data. :(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
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.
 

Colobri

New Member
Joined
Feb 18, 2020
Messages
7
Office Version
2013
Platform
Windows, Web
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
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?
 

Colobri

New Member
Joined
Feb 18, 2020
Messages
7
Office Version
2013
Platform
Windows, Web
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
What macro is being called by the icon on the toolbar?
Also did you delete your code & replace it with mine?
 

Forum statistics

Threads
1,089,638
Messages
5,409,474
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top