Copy range of data from sheet to a new one

[Afarag]

Dears,

I appreciate you help to get answering my issue,
i have a range of data from "A1:BY200" in a sheet that titled "Final" i ask to copy this range and paste it in a new sheet in considering somethings:
>: i need paste special "values and number formats" because the main data have functions that its input changes contentiously.
>: i will copy this data every day and and every one need this data migrated to a new sheet as i will use the pasted sheets as a reference after that,
>: i need the content in cell "C1" in "Final sheet" to be the New Sheet Name

Gratefully,

 

[Luke M]

This should work for you. Remember to change format of your workbook to .xlsm or .xlsb

Sub CopyNew()
Dim wsNew As Worksheet
Dim myRange As Range
Dim strName As String

Application.ScreenUpdating = False
With Worksheets("Final")
    'Can't use / in sheet names, changed format
    strName = Format(.Range("C1").Value, "dd-mmm-yyyy")
    Set myRange = .Range("A1:BY200")
   
    Set wsNew = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets("Final"))
    myRange.Copy
    wsNew.Cells(1, 1).PasteSpecial xlPasteValues
    wsNew.Cells(1, 1).PasteSpecial xlPasteFormats
    wsNew.Cells(1, 1).PasteSpecial xlPasteColumnWidths
    wsNew.Name = strName
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
   
End Sub

 

[Afarag]

Hi Luke

Thanks a lot, your code really get what i looking for and more

Gratefully,

 

[Afarag]

@Luke M

appreciate your brilliant effort to provide me some help as i want a msg box appear when run the code in title " Are you sure to migrate the data to a new sheet?"
can we add it to your code

Cheers,

 

[webmax]

Hi use the below code

Sub CopyNew()
Dim wsNew As Worksheet
Dim myRange As Range
Dim strName As String
Application.ScreenUpdating = False
With Worksheets("Final")
    'Can't use / in sheet names, changed format
   strName = Format(.Range("C1").Value, "dd-mmm-yyyy")
    Set myRange = .Range("A1:BY200")
    If MsgBox("Are you sure to migrate the data to a new sheet?", vbYesNo) = vbYes Then
    Set wsNew = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets("Final"))
    myRange.Copy
    wsNew.Cells(1, 1).PasteSpecial xlPasteValues
    wsNew.Cells(1, 1).PasteSpecial xlPasteFormats
    wsNew.Cells(1, 1).PasteSpecial xlPasteColumnWidths
    wsNew.Name = strName
Else
End If
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub