VBA newbie - need help with code to delete certain cells, rename sheets and ignore master sheet

[Hayley Howells]

Hi there, thanks for reading - I'm a total newbie to VBA so this is a steep learning curve for me. I am working on a file for person identifiable information to be collected, but the file needs code to anonymise the information before it can be returned to me. Essentially I have a "Master" sheet to collect data for each person, which is hidden, and another sheet called "Instructions".

I already have code for the master sheet to be replicated using a button for adding a new patient, which includes a message box to write the patient's name into the tab name, so whilst data is collected, the workbook will contain a variable number of worksheets all named after individual people.

What I want to do is create another button to be able to clear the contents of cells b2 to c7 in each of the sheets named after a patient, and also to rename all the patient sheets, something unique like patient 1, patient 2 etc, all the while ignoring the master and instruction sheets. Is this do-able?

Any help appreciated.

 

[JackDanIce]

Welcome to the board. This code should get you started, though you should make a copy of your file before testing it:

Sub FormatWorkbook()

Dim i as Long, j as Long
j = 1

Application.ScreenUpdating = False

For i = 1 to Worksheets.Count
  With Sheets(i)
    Select Case .Name
      Case "Master", "Instructions"
        'Do Nothing
      Case Else
         .Range("B2:C7").ClearContents
         .Name = "Patient " & j
         j = j + 1
    End Select
Next i

Application.ScreenUpdating = True

End Sub

 

[Hayley Howells]

Hiya, thank you so much for your help. I have tried this code in my workbook but it returns an error "compile error: next without for", and the "Sub FormatWorkbook" text on line 1 is highlighted in yellow.... hoping you can help... so close!

 

[JackDanIce]

My bad for trying to write out the code without testing it. Missing line in red, try:

Sub FormatWorkbook()

Dim i As Long, j As Long
j = 1

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    With Sheets(i)
        Select Case .Name
            Case "Master", "Instructions"
                'Do Nothing
            Case Else
                .Range("B2:C7").ClearContents
                .Name = "Patient " & j
                j = j + 1
        End Select
    End With
Next i

Application.ScreenUpdating = True

End Sub

 

[ngocdiep1510]

I think Jack'code missed to close "With". You just need to add "End with".
He fixed it already :D

I dont understand the problem with "the "Sub FormatWorkbook" text on line 1 is highlighted in yellow"

 

[Hayley Howells]

That works seamlessly..... thank you so much!! The world would be lost without support like this!!

 

[JackDanIce]

You're very welcome, thanks for the feedback