Results 1 to 6 of 6

Thread: VBA copy sheets as values only

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA copy sheets as values only

    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

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA copy sheets as values only

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA copy sheets as values only

    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.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA copy sheets as values only

    Are the sheets password protected? Are they all protected or just some? If some, which ones are protected?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA copy sheets as values only

    Yes, all sheets have some locked cells and there is a password to unlock.

    Thanks

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,298
    Post Thanks / Like
    Mentioned
    95 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA copy sheets as values only

    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:="MyPassword"
                With desWB
                    ws.Copy .Sheets(.Sheets.Count)
                    ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
                End With
                ws.Protect Password:="MyPassword"
            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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •