How to copy sheet and move to front of workbook?

[Mr2017]

Hi

I've like to

- copy sheet 9 in a workbook
- move it to the front of the workbook
- then copy and paste everything in that sheet as values


Does anyone know how to do this, please? I know the code below will activate it then move it to the front.

The methods I've seen online copy it to a new workbook but Ito copy it and keep the copy within the workbook.

Sub FN()


Sheet9.Activate
<code class="vb plain" style="font-size: 16px; white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">ActiveSheet.Move Before: = ActiveWorkbook.Sheets(1)</code>


End Sub

 

[tyija1995]

Hey,

Try this code:

Sub FN()
Sheets("Sheet9").Select
    Sheets("Sheet9").Copy Before:=Sheets(1)
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

 

[welshgasman]

You could even use the Macro recorder to get the bulk of the code to start with?

 

[tyija1995]

Just as welshgasman says, I actually got the first 2 lines of that code from the macro recorder! The final line just converts all the used cells to values instead of formulae.

 

[Michael M]

Don't need to select
and you might want to rename it !

Sub MM1()
Sheets("Sheet9").Copy Before:=Sheets(1)
 With ActiveSheet
    .UsedRange.Value = .UsedRange.Value
    .Name = "NEW SHEET"
 End With
End Sub