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=""
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=""