Creating new sheet

[The_Steward]

Hey,

I need some help figures out solutions to the problems below. I have created a basic macro using the recorder but it is limited in helping me solve these problems.

Problem Description:

1.
I need to create a commandbutton that allows users to add new clients to the workbook.

But to do this properly they need to add a new sheet and then automatically rename the sheet to the next value in the column.

i.e if had a list which goes client1, client2, client3 etc, then I need the button to perform an operation that creates a new sheet, and renames it to client1, then when clicked again adds a new sheet and renames it client2 and so forth.

I also need setting and data from a template sheet automatically copy + pasted so that each new sheet is set up the same way.

End solution should automatically have a cell selected (i.e "E5") in this new sheet so users can start entering data.

2.
This leads to my 2nd problem, updating the workbook.
Now there may be a point where someone adds up to a 100 clients or more, and then I need to update their workbook. it would be too time consuming and tedious to edit all these sheets, so I need a commandbutton that links to a macro that loops through all the sheets that have a matching name within my column of client1, client2, client3 etc and updates all these sheets to exactly match the template sheet. (Please note: i'm not worried about data being overidden as I am working on a solution for this that allows users to automatically have their saved data uploaded into each sheet they've created when it's updated)

Any help or suggestions are much appreciated.

 

[Legacy 143009]

1st request is easy. Create an empty template with desired formatting. To create new client sheet you can use the code below:

Sub createNewWorksheet()
  Dim lRow As Integer
  Dim nameIndex As Integer
  Dim ws As Worksheet
  Dim newName As String
  lRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
 
  With Worksheets("Data") 'Whatever the data sheet name is
  newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("A2:A" & lRow), 0), 0) + 2, 1).Value 'Look Column A for client names. Assuming Column A has headers.
  End With
  Sheets("Template").Copy After:=Sheets(Sheets.Count)
  Set ws = ActiveSheet
  ws.Name = newName
End Sub

I didn't understand your 2nd request exactly. Do you want the data or formatting to be updated in each sheet? Also, how will command button know which client to update? Is all client names going to have their own dedicated button? I need more information about this one.

 

[The_Steward]

1st request is easy. Create an empty template with desired formatting. To create new client sheet you can use the code below:

Sub createNewWorksheet()
  Dim lRow As Integer
  Dim nameIndex As Integer
  Dim ws As Worksheet
  Dim newName As String
  lRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
 
  With Worksheets("Data") 'Whatever the data sheet name is
  newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("A2:A" & lRow), 0), 0) + 2, 1).Value 'Look Column A for client names. Assuming Column A has headers.
  End With
  Sheets("Template").Copy After:=Sheets(Sheets.Count)
  Set ws = ActiveSheet
  ws.Name = newName
End Sub

I didn't understand your 2nd request exactly. Do you want the data or formatting to be updated in each sheet? Also, how will command button know which client to update? Is all client names going to have their own dedicated button? I need more information about this one.

Hey Flashbond,

Thanks for your help!

Firstly, I edited your macro to fit my workbook. Everything worked except for last line 'ws.Name = NewName'. This might because I didn't include headers in my row selection though?

Please note participant is industry name standard for client in my field. I also just decided it would be easiest to make the first client (participant1/R4) the designated template sheet.

Sub createNewWorksheet()
  Dim lRow As Integer
  Dim nameIndex As Integer
  Dim ws As Worksheet
  Dim newName As String
  lRow = Worksheets("Code and Data Centre").Cells(Rows.Count, 1).End(xlUp).Row
 
  With Worksheets("Code and Data Centre") 'Whatever the data sheet name is
  newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("R4:R1003" & lRow), 0), 0) + 2, 1).Value 'Look Column A for client names. Assuming Column A has headers.
  End With
  Sheets("Participant1").Copy After:=Sheets(Sheets.Count)
  Set ws = ActiveSheet
  ws.Name = newName
End Sub

Secondly, the answer is both. I anticipate I will need to make both formatting and data changes as I get various requests from users. I just want one button that updates all the client sheets at the same time to match the template sheet that I can press once i'm satisfied with my edits to the client template sheet.

Once I finish updating the workbook I use an excel add in to compile the workbook into a exe application. This then allows users to save the data they enter into a xlsce file, which gets uploaded into the exe application each time they open it. This essentially allows the exe application to act as a template that I can continue to edit and update without impacting the data that users have entered.

I hope that makes a bit more sense.

 

[Legacy 143009]

Ok, I modified the first code per your need. (participant1/R4)

Sub createNewWorksheet()
  Dim lRow As Integer
  Dim nameIndex As Integer
  Dim ws As Worksheet
  Dim newName As String
  lRow = Worksheets("Code and Data Centre").Cells(Rows.Count, 18).End(xlUp).Row 'Now it will find the last record in Column 18 'R'
 
  With Worksheets("Code and Data Centre")
  newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("R1:R" & lRow), 0), 3) + 1, 1).Value 'So you don't have to specify as 1003
  End With
  Sheets("Participant1").Copy After:=Sheets(Sheets.Count)
  Set ws = ActiveSheet
  ws.Name = newName
End Sub

For the second code I assume you will use "Participant1" template sheet. And you don't want to touch "Participant1" and "Code and Data Centre" sheets:
Sub copyData()
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name <> "Participant1" And ws.Name <> "Code and Data Centre" Then
Dim DataToCopy As Range
Set DataToCopy = Sheets("Participant1").Range("A1:R100") 'You should set your own used range from Participant1 sheet.
If Not DataToCopy Is Nothing Then
DataToCopy.Copy
ws.Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, SkipBlanks:=True 'Again you should set your own target range other than A1 in client sheets.
End If
End If
Next
End Sub

Once I finish updating the workbook I use an excel add in to compile the workbook into a exe application. This then allows users to save the data they enter into a xlsce file, which gets uploaded into the exe application each time they open it. This essentially allows the exe application to act as a template that I can continue to edit and update without impacting the data that users have entered.

No, I don't know that addon really.

 

[The_Steward]

Ok, I modified the first code per your need. (participant1/R4)

Sub createNewWorksheet()
  Dim lRow As Integer
  Dim nameIndex As Integer
  Dim ws As Worksheet
  Dim newName As String
  lRow = Worksheets("Code and Data Centre").Cells(Rows.Count, 18).End(xlUp).Row 'Now it will find the last record in Column 18 'R'
 
  With Worksheets("Code and Data Centre")
  newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("R1:R" & lRow), 0), 3) + 1, 1).Value 'So you don't have to specify as 1003
  End With
  Sheets("Participant1").Copy After:=Sheets(Sheets.Count)
  Set ws = ActiveSheet
  ws.Name = newName
End Sub

For the second code I assume you will use "Participant1" template sheet. And you don't want to touch "Participant1" and "Code and Data Centre" sheets:
Sub copyData()
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name <> "Participant1" And ws.Name <> "Code and Data Centre" Then
Dim DataToCopy As Range
Set DataToCopy = Sheets("Participant1").Range("A1:R100") 'You should set your own used range from Participant1 sheet.
If Not DataToCopy Is Nothing Then
DataToCopy.Copy
ws.Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, SkipBlanks:=True 'Again you should set your own target range other than A1 in client sheets.
End If
End If
Next
End Sub


No, I don't know that addon really.

Thanks for this! the first macro is still coming up with same error, it creates the new worksheet fine, but just can't rename it.

lRow is counting to 1003 fine, and I tried messing around and got it to work on another column with only one cell filled, but just not on column R.

for the second macro. I have another sheets that I don't want the formatting and data copied to. How can I ensure that data is only copied to sheets that names match those found in column R of "Code and Date Centre"

 

[Legacy 143009]

Thanks for this! the first macro is still coming up with same error, it creates the new worksheet fine, but just can't rename it.

I don't know. Maybe there is something wrong with the names. Maybe an invalid character, maybe too long. It works fine for me when I test it. Also you are limited to 255 sheets in Excel which is far less than 1003 rows.

Oh yes, I totally forgot the names for the second code. You can use then:

Sub copyData()
  Dim workSheets As Variant
  Dim lRow As Integer
  lRow = Worksheets("Code and Data Centre").Cells(Rows.Count, 1).End(xlUp).Row
 
  ws = Sheets("Sheet").Range("R4:R" & lRow).Value
  For Each ws In workSheets
    With Worksheets(ws)
      Dim DataToCopy As Range
      Set DataToCopy = Sheets("Participant1").Range("A1:R100") 'You should set your own used range from Participant1 sheet.
      If Not DataToCopy Is Nothing Then
        DataToCopy.Copy
        .Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, SkipBlanks:=True 'Again you should set your own target range other than A1 in client sheets.
        'SkipBlanks enables to keep of destination data if the source data is empty. Delete it if you want to override values.
      End If
    End With
  Next
End Sub

 

[Legacy 143009]

Ok I found the fault for the first Code. I am missing the column number in the naming line.

newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("R1:R" & lRow), 0), 3) + 1, 18).Value

You should have 18 at the very end.
What this line does is, it looks up the last sheet name in a match formula. If error, returns 3. If any value is matched or no value was found it adds +1 which is either the first name (if error) or next name in the column 18. I hope it makes sense.

 

[Legacy 143009]

Also in the second code, you should change this:

ws = Sheets("Sheet").Range("R4:R" & lRow).Value

to this:

workSheets = Sheets("Sheet").Range("R4:R" & lRow).Value

Sorry, it is very early in the morning here. Still trying to waking up

 

[The_Steward]

Ok I found the fault for the first Code. I am missing the column number in the naming line.

newName = .Cells(Application.IfError(Application.Match(Sheets(Sheets.Count).Name, .Range("R1:R" & lRow), 0), 3) + 1, 18).Value

You should have 18 at the very end.
What this line does is, it looks up the last sheet name in a match formula. If error, returns 3. If any value is matched or no value was found it adds +1 which is either the first name (if error) or next name in the column 18. I hope it makes sense.

Yes that worked like a treat! Thankyou

Thanks for the note about the number of sheets. 1000 was just an arbitrary number I was using, so i've limited it down to 100, which is a more realistic maximum for the users.

No need to answer this, as I already appreciate all your efforts so far, but i'm starting to think it will be necessary for me to create and rename all sheets at once for users as well. Is there a way I could do that too? As well as delete all these sheets at once? (i'll rename the template sheet from participant1 to participant template if I do this to make it less complicated)

 

[Legacy 143009]

I didn't understand your question. Are you going to rename using a naming scheme?
Basically it will go like this:

Sub myFunction()
  Dim ws As Worksheet
  Dim lRow As Integer
  lRow = Worksheets("Code and Data Centre").Cells(Rows.Count, 18).End(xlUp).Row
  For Each ws In Worksheets
    If ws.Name <> "Participant1" And ws.Name <> "Code and Data Centre" Then 'Put a condition if you want to exclude any
      For i = 4 to lRow 
        If ws.Name = Worksheets("Code and Data Centre").Cells(i, 18).Value Then
          'Do anything here if any worksheet name matches any name in the list.
        End If 
      Next
    End If
  Next
End Sub