new to VBA= find and replace part of the VBA formula

kmowers07

New Member
Joined
Jun 7, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
hello Excel Experts,
I am new excel vba, i have looked at forums, google and youtube for this answer. I am duplicating a sheet and renaming its sheet name and table name. the issue is i need some help to figure how to rename the sheet and range automatically in the VBA code. this is the code i have to replace table and and sheet name.... but i need to replace the oldtbl name and oldsheet name in a formulas.

Sub Button1_Click()
Sheet1.Copy before:=Sheet3

Dim shName As String
Dim Currentname As String
Dim tblName As String
Dim OldName As String
Dim ws As Worksheet

Currentname = ActiveSheet.Name
shName = InputBox("What is the sheet name?")
tblName = InputBox("What is the table name?")
ThisWorkbook.Sheets(Currentname).Name = shName

Sheets(shName).ListObjects(1).Name = tblName

End Sub

Dim wb as workbook
Dim ws as worksheets
set ws=wb.worksheets("OldSheet")

Range("Oldtbl[Colors]").formula=""
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,213,583
Messages
6,114,487
Members
448,575
Latest member
hycrow

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