VBA copy sheets as values only

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
151
Morning All,

I have tabs “Info”, “Dash”, “Projects”, Dates” and “DatesInfo”.

I would like to copy them to a new workbook with the same formatting but values only to remove the formulas. I would like to save the file name as ‘Performance Dashboard - DD/MM/YYYY’ with today’s date in the same directory as the original.

Many thanks
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,610
Try:
Code:
Sub CopySheets()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook, ws As Worksheet
    Set srcWB = ThisWorkbook
    Set desWB = Workbooks.Add(1)
    With srcWB
        For Each ws In .Sheets(Array("Info", "Dash", "Projects", "Dates", "DatesInfo"))
            With desWB
                ws.Copy .Sheets(.Sheets.Count)
                ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
            End With
        Next ws
    End With
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs Filename:=srcWB.Path & Application.PathSeparator & "Performance Dashboard - " & Format(Date, "dd-mm-yyyy")
    Application.ScreenUpdating = True
End Sub
 

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
151
Hi @mumps, thank you.

That's seems to do the job but I forgot that some of the sheets have protected cells, and I would like to carry over the protection too.

The error is" Run-time error '1004': The cell or chart you're trying to change is on a protected sheet".

Many thanks again. :)
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,610
Are the sheets password protected? Are they all protected or just some? If some, which ones are protected?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,610
Try the following macro. Replace "MyPassword" (in red) with your actual password.
Code:
Sub CopySheets()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook, ws As Worksheet
    Set srcWB = ThisWorkbook
    Set desWB = Workbooks.Add(1)
    With srcWB
        For Each ws In .Sheets(Array("Info", "Dash", "Projects", "Dates", "DatesInfo"))
            ws.Unprotect Password:="[COLOR="#FF0000"]MyPassword[/COLOR]"
            With desWB
                ws.Copy .Sheets(.Sheets.Count)
                ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
            End With
            ws.Protect Password:="[COLOR="#FF0000"]MyPassword[/COLOR]"
        Next ws
    End With
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs Filename:=srcWB.Path & Application.PathSeparator & "Performance Dashboard - " & Format(Date, "dd-mm-yyyy")
    Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,085,477
Messages
5,383,910
Members
401,865
Latest member
L Javier

Some videos you may like

This Week's Hot Topics

Top