record worksheet name into a cell

[coolman]

hello

i have been trying to get the program to record the worksheet name into a cell but i could not get it working.

anyone can advice? here is my code

Sub ButtonPRGenerator_Click()


x = MsgBox("Welcome to Purchase Request Generator.", vbOKOnly + vbInformation, "Purchase Request Generator")


Dim a, b
Sheets.Add after:=Sheets(Sheets.Count)
a = Sheets.Count
b = InputBox("Please enter the sheet name for Purchase Summary Report. Do note that the name of the worksheet must not be similar as the previous worksheets.")
Sheets(a).Name = b


With Worksheets("purchase request list").Cells(lastrow + 1, 8)
.Value = Sheets(a).Name
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.WrapText = True
End With


PRGeneratorS1.Show


End Sub

 

[Norie]

Where are you setting the value of lastrow?

 

[mrshl9898]

Is b Recording the name correctly?

.Value = b

 

[Rick Rothstein]

i have been trying to get the program to record the worksheet name into a cell but i could not get it working.

anyone can advice?

In what way is your code not working correctly? Structurally, there is nothing wrong with the code although I would think you might want to calculate a value for your "lastrow" variable so that the object of your With statement does not always put the sheet name in cell H1.

 

[coolman]

i forgot to put in the last row calculation.

after adding it, the program is able to work

thank you for all of your advice

In what way is your code not working correctly? Structurally, there is nothing wrong with the code although I would think you might want to calculate a value for your "lastrow" variable so that the object of your With statement does not always put the sheet name in cell H1.