Kirsty has attended:
Excel Advanced course
Worksheet name inputted to cell on a summary sheet
Basically I have done a Userform for our restaurants to fill out with details from a customer review questionnaire and I have enabled these details to be input straight onto a spreadsheet. I have then created a macro so that at the end of each month the spreadsheet can be inserted onto a new worksheet and then the worksheet is named as the date inputted into cell B1 on the original 'Data' spreadsheet. But, I would also like to create a 'Summary' spreadsheet, so I would like the name of the worksheet to be inserted into the first row of the Summary sheet each time a new worksheet is created?
RE: Worksheet name inputted to cell on a summary sheet
Hi Kirsty
Thanks for getting in touch. There are lots of useful properties tucked away in the Application object. So if you wanted to get the filename into the current cell, you would type:
ActiveCell = Application.ThisWorkbook.FullName
Or to insert a row at the top of the worksheet and enter this you could put:
Rows("1:1").Insert Shift:=xlDown
Range("A1") = Application.ThisWorkbook.FullName
I hope this helps. Let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Worksheet name inputted to cell on a summary sheet
Hi Gary,
Thank you very much, that's great. Just another question...
How would I then enable each next new worksheet name to automatically go into the next cell. For example A1= worksheet 1 name, cell B1 would then be worksheet 2 name etc.
Many thanks for your help with this.
Kirsty
RE: Worksheet name inputted to cell on a summary sheet
Hi Kirsty
Thanks for your reply. You can reference the worksheet name with ActiveSheet.Name.
So to insert in a cell:
Range("A1") = ActiveSheet.Name
However here's a loop of code that will retrieve all the sheet names and list them in the same row, from left to right starting from the current cursor location:
Sub SheetNamesInARow()
For i = 1 To Sheets.Count
ActiveCell.Offset(0, i) = Sheets(i).Name
Next i
End Sub
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Worksheet name inputted to cell on a summary sheet
Hi Gary,
Sorry to be a pain, but it isnt quite what I need.
The worksheets will be added at the end of every month, therefore I needed the worksheet name to be added to the summary sheet as soon as the new worksheet is added. Rather then the worksheet names to be inputted after many worksheets have been added.
Therefore, at the end of the month the worksheet will be added and it will automatically be named "31072013" for example and then this name will go into cell A1. Then at the end of the next month, the worksheet will be added and named "31082013", then this name will go into the next cell along on the Summary sheet (cell B1.)
I hope this makes sense.
Sorry for the confusion,
Kirsty
RE: Worksheet name inputted to cell on a summary sheet
Hi Kirsty
Thanks for clarifying. I'd be wary of having the macro automatically do this when a sheet is added, as sheets could be created by mistake. However you could run the following lines in a macro whenever you are ready to do so:
ActiveSheet.Name = Format(Date, "ddmmyyyy")
Sheets("Summary").Range("a1").End(xlToRight).Offset(0, 1) = ActiveSheet.Name
This renames the current sheet to today's date, then on the Summary sheet goes right as far as it can from cell A1, and adds in the name. You should test this on your own data as you may have things I'm not expecting in your sheet.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
