VBA Changing values in cells in table according to sheet name

JudeBarak

New Member
Joined
Apr 21, 2014
Messages
6
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!

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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,216,124
Messages
6,128,993
Members
449,480
Latest member
yesitisasport

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top