Set value of another worksheets cell range

[rharri1972]

Good Saturday morning everyone!!

What I want to do if someone doesn't mind helping me is....
I have a macro that will check to make sure ALL cells in a range are empty on sheet ("paste MRP data here"). I have a user form that is triggered when criteria is met. At this point I am on "Sheet1". When i hit the "OK" button on the userform I want to set the value of A1 on sheet ("paste MRP data here") to "Paste Data Here"

I am trying with :
sub setvalue()
application.screenupdating = false
worksheets("paste MRP data here").select
activesheet.range("A1").value = "Paste Data Here"
worksheets("Sheet1").activate
application.screenupdating = true
end sub

I keep getting a subscript out of range error 9

as you can probably see i am new to this and trying to wrap my head around it as well.
any help is greatly appreciated!!

 

[BigBeachBananas]

The macro you presented here works fine on its own. I suspect that the user form/empty range macro is causing the problem. Maybe you can share that macro.

 

[jasonb75]

The fact that your code is all in lower case tells me that you've typed it here and not posted your actual code.

Although it could be done much simpler, there is no reason for it to give you runtime error 9 other than the possibility that you have misspelled one of the worksheet names.

Before you do much more, I suggest that you take steps to un-learn the use of Select, it is a command that is never needed and only clutters your code.

Sub setvalue()
Worksheets("paste MRP data here").Range("A1").Value = "Paste Data Here"
End Sub

 

[rharri1972]

The fact that your code is all in lower case tells me that you've typed it here and not posted your actual code.

Although it could be done much simpler, there is no reason for it to give you runtime error 9 other than the possibility that you have misspelled one of the worksheet names.

Before you do much more, I suggest that you take steps to un-learn the use of Select, it is a command that is never needed and only clutters your code.

Sub setvalue()
Worksheets("paste MRP data here").Range("A1").Value = "Paste Data Here"
End Sub

thank you for that tip Jason!! Still new so nothing is "stuck" at this moment. LOL

 

[rharri1972]

The fact that your code is all in lower case tells me that you've typed it here and not posted your actual code.

Although it could be done much simpler, there is no reason for it to give you runtime error 9 other than the possibility that you have misspelled one of the worksheet names.

Before you do much more, I suggest that you take steps to un-learn the use of Select, it is a command that is never needed and only clutters your code.

Sub setvalue()
Worksheets("paste MRP data here").Range("A1").Value = "Paste Data Here"
End Sub

Jason, would it have anything to do with the fact this code is in the "command button" of the userform?

 

[rharri1972]

The fact that your code is all in lower case tells me that you've typed it here and not posted your actual code.

Although it could be done much simpler, there is no reason for it to give you runtime error 9 other than the possibility that you have misspelled one of the worksheet names.

Before you do much more, I suggest that you take steps to un-learn the use of Select, it is a command that is never needed and only clutters your code.

Sub setvalue()
Worksheets("paste MRP data here").Range("A1").Value = "Paste Data Here"
End Sub

This is copied straight from my vba editor...
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Worksheets("paste MRP data here").Range("A1").Value = "Paste Data Here"
Worksheets("Sheet1").Activate
Application.ScreenUpdating = True
End Sub

 

[rharri1972]

The fact that your code is all in lower case tells me that you've typed it here and not posted your actual code.

Although it could be done much simpler, there is no reason for it to give you runtime error 9 other than the possibility that you have misspelled one of the worksheet names.

Before you do much more, I suggest that you take steps to un-learn the use of Select, it is a command that is never needed and only clutters your code.

Sub setvalue()
Worksheets("paste MRP data here").Range("A1").Value = "Paste Data Here"
End Sub

thank you Jason...it is working. I just breathed a little...took my time...retyped it and it works. Thanks for your guidance!!

 

[jasonb75]

Did you try it with the code from my post? That will do the same as yours but skips out all of the unnecessary steps.

 

[rharri1972]

Did you try it with the code from my post? That will do the same as yours but skips out all of the unnecessary steps.

yes i did. thank you!