Macros?

[LRay67]

First Scenario: We are trying to copy over data from a range of cells from
one worksheet into added request(s) sheets that users have already added once
return to us. I have created the following to repeat the copying of the
first added sheet, but the user can add multiples and want this to loop
through the rest of the worksheets added without errors coming up due to the
worksheet name change Request (2) to Request (3) and so forth?

Range("P5:R5").Select
Range("P5:R5").Copy
Sheets("Request (2)").Select
Range("P5:R5").Select
ActiveSheet.Paste

Second Scenario: We are trying to have the worksheet to be renamed once the
user types in an user's name in the TEXT BOX.....unable to get this to work.
We know how to do this when working with Excel cells itself, but not a TEXT
BOX? Any suggestions??

Any help would be greatly appreciated it.

Linda

 

[Per Jessen]

Hi Linda
I assume that you always copy from "sheet1"

Range("P5:R5").Copy
For sh = 2 To Sheets.Count
If Sheets(sh).Name Like "Request*" Then
ActiveSheet.Paste Destination:=Sheets(sh).Range("P5:R5")
End If
Next

Assuming that the TextBox is on the worksheet, exit design mode and
paste this code to the code sheet for the desired worksheet.

Private Sub TextBox1_LostFocus()
ActiveSheet.Name = Me.TextBox1.Value
End Sub

Regards,

Per

 

[LRay67]

Jessen, Thanks for the information. The first scenario work for me. I am
still working with the second recommendation.....I am not a macro wiz or
programmer....so taking me awhile to figure out. I finally found the the
code sheet, but doesn't seem to work when just placing in there. Do I have
to associate anything else to this when specifying Private???

If you could if we were to change the names relating to the sheets without
having the norm of ("Request*) as the tab name how would I do that? Can I
add a statement to the first recommendation that you did? So it will update
that name source after the cells from range P5:R5 are copied??

Thanks for your help...

Linda