Macro help rename sheet

[Dharris144]

I need help on a macro. I want to copy the current sheet to a new sheet with a pop up box asking for the new sheet name. Then I want to copy all cells on the new sheet and paste as values to remove all formulas. Thanks in advance for any help you can give me.

 

[James Snyder]

There are several ways of doing this including using a MsgBox, but I haven't used them this way. Here is a resource:
InputBox and MsgBox

Once you recover the choice as a string, you can do something like this:

sheets(1).name = "Users Choice"

The copy part goes something like this:

Worksheets("sheet1").Range("a1:a" & Range("a65536").End(xlUp).Row).Copy _ 
        Destination:=Worksheets("sheet2").Range("A1")

 

[texasalynn]

try something like this

FName = InputBox("What is name of new sheet?", "Need Workbook Name")
ActiveSheet.Name = FName
Cells.Copy
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

 

[Dharris144]

Texasalynn Thank you very much this did the trick. I really appreciate everyone's help.

 

[Dharris144]

I pasted the above into my macro but now it is just renaming the same sheet not creating a new one.

 Range("O2:W2").Select
    Selection.Copy
    Sheets("Data").Select
    Range("A600").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:I").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A2:I600")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'   Range("A1").Select
'    Sheets("Pivot table").Select
'    ActiveWorkbook.RefreshAll
'    Sheets("Chart1").Select
'    ActiveChart.ChartArea.Select
'    ActiveChart.PlotArea.Select
'    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
'
    Sheets("Customer Complaint Report").Select
    Range("A1").Select
        FName = InputBox("What is name of new sheet?", "Need Workbook Name")
    ActiveSheet.Name = FName
    Cells.Copy
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues    
            Sheets("Customer Complaint Report").Select
    Range( _
        "I7:J7,D9:F9,I9,J9,D11:F11,J11,D12,D13,D15,E15,F17,G17,H17,I17,J17,F18:J18,C18:D18,A21:J25" _
        ).Select
    Range("A21").Activate
    ActiveWindow.SmallScroll Down:=18
    Range( _
        "I7:J7,D9:F9,J9,J11,D12,D13,D15,E15,F17,G17,H17,I17,J17,F18:J18,C18:D18,A21:J25,D27,J27,D34:J34,D36,E36:J36,D37:J37,D38:J38,D40:J40,E42,G42,E44,G44" _
        ).Select
    Range("G44").Activate
'    Selection.ClearContents
    Range("D7").Select
End Sub

 

[Dharris144]

I have tried to fix it but I am still just renaming the same sheet not creating a new sheet with the file name DOH

 

[James Snyder]

Try adding something like:

Dim newSheet As WorkSheet

Set newSheet = ActiveWorkbook.Add
With newSheet
newSheet.Name = FName
...
End With

From Microsoft:

expression .Add(Before, After, Count, Type)
expression A variable that represents a Sheets object.
Parameters

Name	Required/Optional	Data Type	Description
Before	Optional	Variant	An object that specifies the sheet before which the new sheet is added.
After	Optional	Variant	An object that specifies the sheet after which the new sheet is added.
Count	Optional	Variant	The number of sheets to be added. The default value is one.
Type	Optional	Variant	Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template. The default value is xlWorksheet.

<tbody>
</tbody>

To use:

Dim newSheet As WorkSheet

Set newSheet = ActiveWorkbook.Add Type:=xlWorksheet
With newSheet
newSheet.Name = FName
...
End With

 

[Dharris144]

Thanks everybody it has been a huge help.