Changing values of ranges on two worksheets with VBA

rizzo93

Active Member
Joined
Jan 22, 2015
Messages
250
I want to reset the values of a range of cells on two worksheets at the same time when I click a button: Periscope and GhostData.


For the one on Periscope, is there a more efficient way of doing that compared to this?
Sub ClearAssigned()

Worksheets("Periscope").Activate

Range("BY3").Value = False
Range("BY4").Value = False
Range("BY5").Value = False
Range("BY6").Value = False
Range("BY7").Value = False
Range("BY8").Value = False
Range("BY9").Value = False
Range("BY10").Value = False
End Sub​


In the same code above, I also want to change the values in a column in a table on GhostData to 0.


I believe the appropriate first step is to activate that worksheet, but how do I change the values in GhostDataTable[Color] without having to call out each cell by name as there are currently 117 of them?
Worksheets("GhostData").Activate
ActiveSheet.Range ("GhostDataTable[Color]").......?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You can write your sub like
Code:
Sub ClearAssigned()
Worksheets("Periscope").Range("BY3:BY10").Value = False
End Sub
 

rizzo93

Active Member
Joined
Jan 22, 2015
Messages
250
Wow, Fluff, that was almost too easy! :)

It worked. Thanks so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,019
Messages
5,526,290
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top