code check

[Alisya]

Hi, i have this code which keeps the below sheets and deletes all other sheets, in the below statement i would like the code to copy and pastevalues for each of the below sheets, how can add this to the code

With ThisWorkbook
For Each WS In .Sheets
Select Case WS.Name
Case "Ratio", "Report", "Task"
Case Else: WS.Delete
End Select
Next

 

[stanleydgromjr]

Alisya,

to copy and pastevalues for each of the below sheets

From what sheet(s) to what sheet?

And, what range will be copied?


Have a great day,
Stan

 

[Isabella]

Which sheets Alisya

 

[Alisya]

Alisya,



From what sheet(s) to what sheet?

And, what range will be copied?


Have a great day,
Stan

Stan, this is the full code i found from another post and changed to my requirements, sheets "Ratio", "Report", "Task", these sheets i need the whole sheet as paste values

Sub test1()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
myFolder = "R:\Management\Data\" & Year(Date) & "\" & Format$(Date, "mmm")
myName = "Reporting V1.xls"
Application.DisplayAlerts = False
With ThisWorkbook
    For Each WS In .Sheets
        Select Case WS.Name
            Case "Ratio", "Report", "Task"
            Case Else: WS.Delete
        End Select
    Next
    Application.DisplayAlerts = True
    .SaveAs myFolder & "\" & myName
End With
Application.ScreenUpdating = True
End Sub

 

[Alisya]

*bump*

 

[stanleydgromjr]

Alisya,

sheets "Ratio", "Report", "Task", these sheets i need the whole sheet as paste values

Do you want each of these sheets, "Ratio", "Report", "Task", to have all the cells/formulae/etc. copied and then paste special values back on to themselves?


Have a great day,
Stan

 

[tusharm]

Maybe,

            Case "Ratio", "Report", "Task"
                ws.UsedRange.Copy: ws.PasteSpecial xlPasteValues
            Case Else: ws.Delete

Note that the above is "destructive" in that it replaces formulas in the designated worksheets with the values. So, if that is not what you want...

Hi, i have this code which keeps the below sheets and deletes all other sheets, in the below statement i would like the code to copy and pastevalues for each of the below sheets, how can add this to the code

With ThisWorkbook
For Each WS In .Sheets
Select Case WS.Name
Case "Ratio", "Report", "Task"
Case Else: WS.Delete
End Select
Next

 

[Alisya]

That is correct Stan

Alisya,



Do you want each of these sheets, "Ratio", "Report", "Task", to have all the cells/formulae/etc. copied and then paste special values back on to themselves?


Have a great day,
Stan

 

[Alisya]

Maybe,

            Case "Ratio", "Report", "Task"
                ws.UsedRange.Copy: ws.PasteSpecial xlPasteValues
            Case Else: ws.Delete

Note that the above is "destructive" in that it replaces formulas in the designated worksheets with the values. So, if that is not what you want...

i get an error on paste values part

WS.PasteSpecial xlPasteValues

 

[stanleydgromjr]

Alisya,

Try:

            Case "Ratio", "Report", "Task"
                With ws.UsedRange
                    .Value = .Value
                End With
            Case Else: ws.Delete

Have a great day,
Stan