0

I have code to insert the sheet name into a field of each sheet (there are 80+ sheets).

I would like to insert the sheet name to a cell and fill down to the last row of each sheet.

I get errors:

Sub nameSheet()
For Each x In Worksheets
    x.Range("F2" & LastRow) = x.Name
Next x
End Sub
Community
  • 1
  • 1
penone
  • 742
  • 1
  • 10
  • 27
  • 3
    You need to determine `LastRow` for each worksheet. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). Also, add `Option Explicit` to the top of the module and declare all variables. – BigBen Nov 15 '19 at 03:35
  • 1
    ... and of course change `"F2"` to `"F" & LastRow` (once you have populated the variable). BTW, what you are using is not a formula, it's VBA code. A formula is what you would put into a worksheet cell. – teylyn Nov 15 '19 at 03:48

1 Answers1

0

As @BigBen said, you need to define LastRow

there is a special cell property called xlCellTypeLastCell. This is handy to use, as your last cell can be anywhere. Additionally I would do Lastrow + 1, that way you don't overwrite anything.

 Sub nameSheet()
 Dim x As Worksheet

 For Each x In Worksheets
    lastrow = x.Cells.SpecialCells(xlCellTypeLastCell).Row
    x.Range("F" & lastrow + 1) = x.Name
 Next x

 End Sub
Bobby Heyer
  • 531
  • 5
  • 18