Putting Same Data into two Ranges on two worksheets

[aheverett1]

I cannot figure out where i am going wrong with my code here, any help would be appreciated. I originally wrote this code to put the info into 1 of 3 ranges depending on which option button is checked. Now i need to also have it put the data into a separate worksheet in the first blank row. This entire separate worksheet is blank as well by the way.

So here is my code. Everything works great and has no problems with the exception of the red text below. I'm at a loss of why this doesn't work, it's the same code as above, i'm just specifying a different worksheet and range i thought?

---

Private Sub CmdInsert_Click()
Dim FirstBlankCell As Range

If OptionButton1.Value = False And OptionButton2 = False And OptionButton3 = False Then
MsgBox "Must select fixture type"
Exit Sub

End If

If IsBlankTextBox = True Then
MsgBox "Must Enter Location"
Exit Sub
End If

If ComboBox1 = "" Then
MsgBox "Must select Fixture"
Exit Sub
End If

If Fixt_Quantity = "" Then
MsgBox "Must Enter Quantity"
Exit Sub
End If

If OptionButton1.Value = True Then
Range("A10:A50").SpecialCells(xlBlanks)(1).Select

ElseIf OptionButton2.Value = True Then
Range("A52:A64").SpecialCells(xlBlanks)(1).Select
ElseIf OptionButton3.Value = True Then
Range("A66:A105").SpecialCells(xlBlanks)(1).Select
End If

ActiveCell.Value = Location.Text
ActiveCell.Offset(, 1) = Fixt_Quantity.Text
ActiveCell.Offset(, 3) = ComboBox1.Text
ActiveCell.Offset(, 7) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(, 3), Sheets("DataSheet").Range("A70:B444"), 2, False)
ActiveCell.Offset(, 6) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(, 3), Sheets("DataSheet").Range("A70:C444"), 3, False)

If OptionButton2.Value = True Then
Sheets("OptionHelper").Range("A1:A25").SpecialCells(xlBlanks)(1).Select
End If
ActiveCell.Value = Location.Text
ActiveCell.Offset(, 1) = Fixt_Quantity.Text
ActiveCell.Offset(, 3) = ComboBox1.Text
ActiveCell.Offset(, 7) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(, 3), Sheets("DataSheet").Range("A70:B444"), 2, False)
ActiveCell.Offset(, 6) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(, 3), Sheets("DataSheet").Range("A70:C444"), 3, False)

Location.Value = ""
Fixt_Quantity.Value = ""
ComboBox1.Value = ""
OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False

End Sub

 

[Rick Rothstein]

Is the OptonHelper sheet the active sheet? You can only select cells on the active sheet.

 

[aheverett1]

no, it won't be the active sheet. it will be a hidden sheet. That makes sense now though. Is there any way around that?

 

[Rick Rothstein]

no, it won't be the active sheet. it will be a hidden sheet. That makes sense now though. Is there any way around that?

A way around it? Don't select worksheets (or range of cells for that matter), simply set a variable to reference the worksheet (or range) and then use that variable when needed.

 

[aheverett1]

I don't have knowledge on how to do that yet, i will definitely look into it though. For now i suppose i will just set the helper range on the active sheet and hide it that range. Thanks for the help!