Hi guys, (this is my first thread here...)
I have a code that needs to run automaticaly in sheets that are created automaticaly by a button that runs a macro to copy a sheet. As part of the new sheet there is the following Macro that runs on each sheet.
I want coloumn "B" to have the client's name which is really the sheets name.
Every time a line is added it needs to have the name.
It runns well the first time, but then I get an Error on line " Range("B4:B" & n + 3).Value = newTbl "
Help Please!
I have a code that needs to run automaticaly in sheets that are created automaticaly by a button that runs a macro to copy a sheet. As part of the new sheet there is the following Macro that runs on each sheet.
I want coloumn "B" to have the client's name which is really the sheets name.
Every time a line is added it needs to have the name.
It runns well the first time, but then I get an Error on line " Range("B4:B" & n + 3).Value = newTbl "
Help Please!
Code:
Sub RenameTable()
Dim oldTbl As String, newTbl As String
Dim n As Integer
n = Range("C4").CurrentRegion.Rows.Count
Application.ScreenUpdating = True
oldTbl = ActiveSheet.ListObjects(1)
newTbl = ActiveSheet.Name
With ActiveSheet
.ListObjects(oldTbl).Name = newTbl
End With
Range("B:B").EntireColumn.Hidden = False
Range("B4:B" & n + 3).Value = newTbl
Range("B:B").EntireColumn.Hidden = True
Application.ScreenUpdating = False
End Sub