Rename New Sheet as Cell Value on another sheet

[gottimd]

I have the following code:

    Sheets.Add
    Sheets(ActiveSheet.Name).Name = "NEW"
    Sheets("NEW").Select
    Sheets("NEW").Move After:=Sheets(5)
    Sheets("IMPORTED").Select
    Range("A3:G102").Select
    Selection.Copy
    Sheets("NEW").Select
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Range("C1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IMPORTED!R[2]C*IMPORTED!R[1]C[1]"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=IMPORTED!R[2]C*IMPORTED!R2C4"
    Range("C1").Select
    Selection.Copy
    Range("C2:C100").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("G1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IMPORTED!R[2]C*IMPORTED!R[1]C[-3]"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=IMPORTED!R[2]C*IMPORTED!R2C4"
    Range("G1").Select
    Selection.Copy
    Range("G2:G78").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveSheet.Calculate
    
End Sub

Is there a way to to write in the code, to rename the "NEW" Sheet, and rename it as what is in the "IMPORT" sheet cell a1?

 

[TommyGun]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddAndCopy()
    <SPAN style="color:#00007F">Dim</SPAN> import <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> newWS <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    <SPAN style="color:#00007F">Set</SPAN> newWS = Worksheets.Add
    <SPAN style="color:#00007F">Set</SPAN> import = Sheets("IMPORTED")
    
    newWS.Move After:=Sheets(5)
    
    import.Range("A3:G102").Copy Destination:=newWS.Range("A1")
    
    <SPAN style="color:#00007F">With</SPAN> newWS
        .Columns("B:G").EntireColumn.AutoFit
        .Range("C1:C100").FormulaR1C1 = "=IMPORTED!R[2]C*IMPORTED!R2C4"
        .Range("G2:G78").FormulaR1C1 = "=IMPORTED!R[2]C*IMPORTED!R2C4"
        .Calculate
        .Name = import.Range("A1")
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> import = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> newWS = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

NOTE: This is not tested!