Open Excel And work on worksheet

[monagan]

I have searched and searched and could not find anything close to my problem.

I have a command button, cmdsubmit

When clicked

I need it to open up another workbook named timesheets
In this workbook it has worksheets named by the job number

I have a1,a2,a3,
a1- Name
a2- Date
a3-Job number
a4-hours

If cell a3 has a job number in it
I need it to go to that sheet (named the job number)
and find the cell where the name and job intersect and put the value of a4 in it.

TimeSheets looks like this
5/10 5/11 5/12
Bob
John
Bill
Jake

Any help on this is greatly appreciated
Thanks in Advance

 

[RamziSaab]

up to what point do u get to using VBA? do u have a code writtgen or u want one from scractch

 

[monagan]

I don't want code written for me.

I just need some direction

Mostly for the intersection and opening the worksheet by job number

A few hints is all I need

 

[RamziSaab]

for the opensheet have u tried something like this

Sub opensheet_example()
Dim OpenSheet As String

'looks up cell a3 and opens that work sheet
OpenSheet = Sheets("sheet1").Cells(3, 1).Value 'replace sheet one with the name of ur worksheet

Sheets(OpenSheet).Select


End Sub

 

[monagan]

On the
Sheets(OpenSheet).Select

I get a subscript out of range

I can't figure out why

 

[RamziSaab]

basically the code looks up cell a3 and select the work sheet in that workbook with that name ( i think that is what u wanted??). Now I specified to look up cell a3...the subscript out of range means that there is no worksheet with that name in that work book...

hope this helps if u need more guidance try to give me a bit more information on exact structure of the sheet and wht u want to do...

 

[Loomah]

Hi

Add something like this to get you started

Dim wbTS As Workbook

Set wbTS = Workbooks.Open("timesheets")
'Then you need the code Ramzi supplied
'but changed slightly
'OpenSheet = [b]wbts.[/b]Sheets etc

You could then use the Find method to find the row containing the name (lRow) and the column containing the job (iCol) then use
Cells(lRow,iCol)= whatever

or something like that!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

Ramzi,
That did help.

Thanks.

I will work on it, The worksheets were in a different workbook

Thanks Loomah, I will give it a try

 

[monagan]

Thanks Guys it works

I'll post this part in case other have problems.
This is what I came up with.
_____________________________________________________
Sub openTimeSheet()
Dim OpenSheet As String
Dim wbTS As Workbook


OpenSheet = Sheets("sheet1").Cells(17, 4).Value

Set wbTS = Workbooks.Open("C:\Documents and Settings\AMY C\Desktop\TIME SHEETS.xls")

wbTS.Sheets(OpenSheet).Select

End Sub

 

[monagan]

any ideas about finding the intersection cell?

 

[Loomah]

Assuming you have something like names in column A and Jobs in Row 1 you could use something like this

Dim lRow As Long
Dim iCol As Integer
lRow = Columns(1).Cells.Find("NAME", LookIn:=xlValues).Row
iCol = Rows(1).Cells.Find("JOB", LookIn:=xlValues).Column
Cells(lRow, iCol).Select

You'll need to replace NAME & JOB with reference to the relevant cell containing the info you are looking for in a similar way to how Ramzi suggested getting the sheet name.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

If the Job or name was in a different workbook, would it make a difference?

 

[RamziSaab]

The find function works for the sheet selected. So you would have to select that workbook and sheet before you could use it...