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

Hayley Howells

New Member
Joined
May 20, 2014
Messages
6
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
Welcome to the board. This code should get you started, though you should make a copy of your file before testing it:
Code:
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

New Member
Joined
May 20, 2014
Messages
6
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
My bad for trying to write out the code without testing it. Missing line in red, try:
Rich (BB code):
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

New Member
Joined
May 3, 2014
Messages
20
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"
 

Forum statistics

Threads
1,081,846
Messages
5,361,668
Members
400,644
Latest member
ndroger1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top