How do I save a file as a specified cell value, then clear content of all unlocked cells, then close the file, then open another specific file?

Question

I tried the below, but it is not doing exactly what I need it to. Instead of unprotecting the sheet and clearing the contents of grey cells (191, 191, 191) I'd like to change the approach and clear the contents of unprotected cells.

As the file is then "saving as" cell value "B5", any new sheets are created on the newly named sheet. I'd like the sheet to still save as cell value "B5" to the specified path, but then close that workbook and open up the original workbook again.

I cannot seem to make the flow correctly, and have had various run time errors as well.

Sub Button6_Click()
        Dim FileName As String
        Dim CellValue As String

        ' Get the value from cell A1 to use as the file name
        CellValue = ThisWorkbook.Sheets("Adjustment Sheet").Range("B5").Value

        ' Construct the full file path and name
        ' You can change "Sheet1" to your actual sheet name
        ' And "C:\Your\Path\" to your desired folder path
        FileName = "S:\Operations\S2\RP500e Refurb Cost Sheet\" & CellValue & ".xls"

        ' Save the file with the new name
        ThisWorkbook.SaveAs FileName
End Sub
Sub UnprotectSheet()
'Unprotect a worksheet without a password
Sheets("Adjustment Sheet").Unprotect

Range("A1:AA118").Select
For Each Cell In Selection
If Cell.Interior.Color = RGB(191, 191, 191) Then
Cell.ClearContents
End If
Next

End Sub

For Each Cell In Sheets("Adjustment Sheet").Range("A1:AA118") : If Not Cell.Locked Then Cell.ClearContents : Next. No need to Unprotect sheet first. — CDP1802
– CDP1802, Commented Nov 7 at 12:49
If you want to save a copy of the workbook but then keep working in the original file you can use ThisWorkbook.SaveCopyAs FileName See learn.microsoft.com/en-us/office/vba/api/… — Tim Williams
– Tim Williams, Commented Nov 7 at 18:02

Excellor · Accepted Answer · 2025-11-07 11:30:34Z

Here's one way to make a copy of the original. It won't open though (if you like that there's steps to add), so it will keep the original open.


Option Explicit


Sub CopyFileDirect()

Dim fso As Object
Dim CellValue As String
Dim OrigFile As String
Dim FileName As String



Set fso = VBA.CreateObject("Scripting.FileSystemObject")
CellValue = ThisWorkbook.Sheets("Adjustment Sheet").Range("B5").Value
OrigFile = "S:\Operations\S2\RP500e Refurb Cost Sheet\MasterFile.xls" 
'Adjust "MasterFile" to the actual filename you are using as the original file
FileName = "S:\Operations\S2\RP500e Refurb Cost Sheet\" & CellValue & ".xls"


Call fso.copyfile(OrigFile, FileName, False)


End Sub

Now for the arguments fso.copyfile:

First argument is the source path, second argument is the destination file path, third arguments indicates here that you don't want to overwrite existing files, leave blank or set to true if you want to allow overwriting.

I'm not sure how to clear content of unprotected cells, so for now I'll have to leave that bit open for someone else. (I also don't completely understand what you're trying to achieve there.)

MGonet · Accepted Answer · 2025-11-07 15:39:07Z

You can use different ways to clear the contents of unlocked cells, depending on whether the worksheet is protected or not.
When the worksheet is protected, you can simply erase the contents of all cells of the used range with error masking enabled. Only unlocked cells will be deleted.
When the worksheet is not protected, you can use the Range.Replace method with the format pattern set on unlocked cells.
If you do not know in advance whether the sheet will be protected at the time of code execution, it is necessary to check its state and set the protection as required by the technique used, and later restore the initial state.
The first variant of the code:

Sub ClearUnlockedCells1()
  Dim IsProtected As Boolean
  With ActiveSheet
      If .ProtectContents Then
         .Unprotect
         IsProtected = True
      End If
      Application.FindFormat.Clear
      Application.FindFormat.Locked = False
      Cells.Replace "*", "", SearchFormat:=True    ' ActiveSheet
      ' this code does not work in protected sheets      
      Application.FindFormat.Clear
      If IsProtected Then .Protect
  End With
End Sub

The second variant of the code:

Sub ClearUnlockedCells2()
    Dim IsProtected As Boolean
    With ActiveSheet
        If .ProtectContents Then
            IsProtected = True
        Else
            .Protect
        End If
        ' a worksheet must be protected
        On Error Resume Next
        ActiveSheet.UsedRange.Value = vbNullString
        On Error GoTo 0
        If Not IsProtected Then .Unprotect
    End With
End Sub

Collectives™ on Stack Overflow

How do I save a file as a specified cell value, then clear content of all unlocked cells, then close the file, then open another specific file?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related