Save my internship

[DNiersbach]

Hey guys I'm new here and this is my first post.

I'm trying to write a macro that will enter data into a worksheet based on the values entered in the form. I've created the user form but the coding is giving me issues.

I'm trying to write a function that will activate the sheet matching the first two inputs before entering the rest of the data in the sheet.

For example,

B1=7/1/15 and C1=Name

How do I get the macro to activate sheet "Name 07-15" my matching the above cells?

Thank you!

 

[ndsutherland]

Try

Sub DNiersbachtesting()
Dim ws As Worksheet, sname As String
sname = Range("C1") & " " & Format(Range("B1"), "mm-yy")
Set ws = Sheets(sname)
Debug.Print ws.Name
End Sub

 

[Smitty]

Welcome to the Board!

Note that you don't need to activate the sheet in order to write to it. Just reference it as in the example above.

 

[DNiersbach]

I entered that code to a button and it didn't giver an error or jump to a page.

UPDATE: I created a cell on the sheet equal to the name of the sheet. So cell D1 and sheet name are equal to John 07-15. This may make it easier to locate?

 

[ndsutherland]

If you really want to jump to the page, you would change

Debug.print ws.name

to

ws.activate

also, I was presuming 07 is the month not the day. Is that correct?

 

[DNiersbach]

Yes, that would be the month. I ran it and got a run-time error 1004.

EDIT: I'm really new to macros. Just started on Wednesday trying to learn.

Sub DNiersbachtesting()
Dim ws As Worksheet, sname As String
sname = Range("F") & " " & Format(Range("B1"), "mm-yy")
Set ws = Sheets(sname)
ws.Activate
End Sub

 

[Smitty]

You need to give a full range address:

Range("F")

Like Range("F5")

And it's not necessary, but it's good to add .Value:

Range("F5").Value

 

[DNiersbach]

Subscript out of range, Run-time error 9.

Private Sub CommandButton1_Click()

Dim ws As Worksheet, sname As String
sname = Range("F1").Value & " " & Format(Range("B1").Value, "mm-yy")
Set ws = Sheets(sname)
Debug.Print ws.Name


End Sub

 

[Smitty]

The sname string and worksheet name need to be identical.

I'd debug.print sname and see what you get.

 

[DNiersbach]

Not positive what you mean, sorry. My master sheet is named MASTER, but each employee has their own sheet I am trying to have the forum enter data into correctly.

Private Sub CommandButton1_Click()

Dim ws As Worksheet, sname As String
sname = Range("F1").Value & " " & Format(Range("B1").Value, "mm-yy")
Set ws = Sheets(MASTER)
Debug.Print sname


End Sub