Assign value into cell on different worksheet using VBA

[Dspace]

I have this code that runs a simple macro all on one worksheet. I have multiple macros like this that are very similar but hide/unhide different rows of a master price worksheet.

What I'm looking to do is to have this macro enter 1 value into a cell (G1) into a different worksheet named "hardware". I have different sales teams using the same pricing tool but different pay margins based on the cell in G1 (password protected). I've never used a macro that changes values on a different worksheet.

Thanks for the help!

Sub UnHideITSC()
'Unhides everytying without a "1" in column B; our standard sales team/vCIOs
Unprotect Password:="password"
For Each cell In Range("b19:b837")
If cell.Value = 1 Then
cell.EntireRow.Hidden = False
Else:
cell.EntireRow.Hidden = True
End If
Next cell

'hiding grouped rows
Rows("20:27").EntireRow.Hidden = True
Rows("31:35").EntireRow.Hidden = True
Rows("38:40").EntireRow.Hidden = True
Rows("43").EntireRow.Hidden = True
Rows("48:55").EntireRow.Hidden = True
Rows("230:325").EntireRow.Hidden = True
Rows("328:423").EntireRow.Hidden = True
Rows("426:521").EntireRow.Hidden = True
Rows("524:619").EntireRow.Hidden = True

ActiveSheet.Protect Password:="password", AllowFormattingCells:=True
End Sub

 

[6StringJazzer]

Add this line in the place where you want it to happen:

Worksheets("hardware").Range("G1").Value = 1

However, if sheet hardware is password protected you will need more:

With Worksheets("hardware")
   .Unprotect "password"
   .Range("G1").Value = 1
   .Protect "password"
End With

Replace password with whatever your password actually is.

What module is your code in? If it is in a standard module, like Module1, all of your references to things in a sheet like Range("b19:b837") will be assumed to be for the current ActiveSheet. If it is in a worksheet module, they will be assumed to be for that worksheet.

Also you don't need a ":" after your Else. I would suggest removing it to avoid any confusion.

 

[Dspace]

Add this line in the place where you want it to happen:

Worksheets("hardware").Range("G1").Value = 1

However, if sheet hardware is password protected you will need more:

With Worksheets("hardware")
   .Unprotect "password"
   .Range("G1").Value = 1
   .Protect "password"
End With

Replace password with whatever your password actually is.

What module is your code in? If it is in a standard module, like Module1, all of your references to things in a sheet like Range("b19:b837") will be assumed to be for the current ActiveSheet. If it is in a worksheet module, they will be assumed to be for that worksheet.

Also you don't need a ":" after your Else. I would suggest removing it to avoid any confusion.

Problem solved! thank you. And yes. It was password protected so thanks for that add-on. It was needed. I love this message board!