Hi,
I have a workbook with few protected worksheets. I would like to save specific worksheets (protected) into new workbook (paste value only). How can I save protected worksheets into new workbook.
Thanks in advance
Hi,
I have a workbook with few protected worksheets. I would like to save specific worksheets (protected) into new workbook (paste value only). How can I save protected worksheets into new workbook.
Thanks in advance
Try these
Sub SaveOneWorksheet() 'file name and path Fname = "C:\Documents\Target1.xlsx" 'unprotect sheets Sheets("Sheet1").Unprotect Password:="myPassword" 'copy Sheets("Sheet1").Copy 'prepare to paste values With ActiveWorkbook.Sheets("Sheet1").UsedRange .Value = .Value End With 'save as new workbook With ActiveWorkbook .SaveAs Filename:=Fname .Close End With 'protect sheets Sheets("Sheet1").Protect Password:="myPassword" End Sub
Sub SaveSeveralWorksheets() Dim Fname As String, ws As Worksheet 'file name and path Fname = "C:\Documents\Target2.xlsx" 'unprotect sheets Sheets("Sheet1").Unprotect Password:="myPassword" Sheets("Sheet2").Unprotect Password:="myPassword" Sheets("Sheet6").Unprotect Password:="myPassword" 'copy from an array - sheet name in " " and separated by commas Sheets(Array("Sheet1", "Sheet2", "Sheet6")).Copy 'prepare to paste values For Each ws In ActiveWorkbook.Worksheets With ws.UsedRange .Value = .Value End With Next ws 'save as new workbook With ActiveWorkbook .SaveAs Filename:=Fname .Close End With 'protect sheets Sheets("Sheet1").Protect Password:="myPassword" Sheets("Sheet2").Protect Password:="myPassword" Sheets("Sheet6").Protect Password:="myPassword" End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks