Select sheet from cell value

[Guest]

I would like to set the historyWks by the value in a cell.eg wks "Input"
Range D3. This would allow me to input the data to the selected sheet, as in
Rnd1, Rnd2 etc.
TKU
Harry

Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim Rnd2 As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim ComboBox9 As Variant
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D3,D5,D7,D9,D11,D13,D15,D17,D19"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Data")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub

 

[Dave Peterson]

I think...

Change this line:
Set historyWks = Worksheets("Data")
to
Set historyWks = Worksheets("Input").range("D3").value

Or to add a bit of validity...

set historywks = nothing
on error resume next
Set historyWks = Worksheets("Input").range("D3").value
on error goto 0

if historywks is nothing then
msgbox "D3 on Input doesn't contain a good worksheet name"
exit sub
end if

 

[Guest]

Sorry dave, I must be doing something wrong. I receive 424 "Object required"
or "D3 on Input doesn't contain a good worksheet name". What am I doing wrong

 

[Dave Peterson]

You have to put the name of a worksheet that already exists in that workbook in
D3 of the Input worksheet.

My first guess is that you mistyped the name (watch out for leading/trailing
embedded spaces).

 

[Guest]

Sorry Dave, still doesn't work. I can populate the combo with the sheet names
or just type 1 in and still get the same errors.

 

[Dave Peterson]

If you type 1 in the cell and that cell is formatted as general, then the code
will use worksheets(1)--the leftmost worksheet.

If you're trying to put the data on a worksheet named 1, then use:

set historywks = nothing
on error resume next
Set historyWks = cstr(Worksheets("Input").range("D3").value)
on error goto 0

if historywks is nothing then
msgbox "D3 on Input doesn't contain a good worksheet name"
exit sub
end if

If that doesn't help...

What kind of combobox did you use?

Did you use a combobox from the Control toolbox toolbar?
Did you use a dropdown from the Forms toolbar?
Did you use data|validation?

What's in D3 and what's the name of the worksheet that you expected to be
populated--they have to match character by character (upper/lower case doesn't
matter, though).

 

[Guest]

Sorry Dave, still no joy. The sheets are named Rnd1, Rnd2 etc. Your code with
the Cstr generated a type mismatch, deleted Cstr and get an object required
mssage. Typing Rnd1 into box makes no diff, match perfectly. Combo from the
control t/b, nothing from Forms t/b and no validation.
TKU for your help

 

[Dave Peterson]

I see my mistake:

Set HistoryWks = Worksheets(CStr(Worksheets("Input").Range("D3").Value))

Sorry.

Sorry Dave, still no joy. The sheets are named Rnd1, Rnd2 etc. Your code with
the Cstr generated a type mismatch, deleted Cstr and get an object required
mssage. Typing Rnd1 into box makes no diff, match perfectly. Combo from the
control t/b, nothing from Forms t/b and no validation.
TKU for your help

 

[Guest]

Works perfectly Dave. Thank you so very much for your patience and
assistance, it is greatly appreciated.
Grid

 

[Dave Peterson]

Nope. Thanks for your patience. It was my mistake (lack of a simple test)!

Works perfectly Dave. Thank you so very much for your patience and
assistance, it is greatly appreciated.
Grid