98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Worksheet name inputted to cell on a summary sheet | Excel forum
Worksheet name inputted to cell on a summary sheet | Excel forum
Resolved · Low Priority · Version 2010
Tue 30 Jul 2013, 16:12Delegate Kirsty said...
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?
Wed 31 Jul 2013, 16:37Trainer Gary said...
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
Thu 1 Aug 2013, 09:08Delegate Kirsty said...
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
Thu 1 Aug 2013, 09:23Trainer Gary said...
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
Thu 1 Aug 2013, 10:17Delegate Kirsty said...
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
Thu 1 Aug 2013, 15:42Trainer Gary said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quickly copy a formula across sheetsSuppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets. |
