CODEWITHSUNDEEP
vbaexcel
Ferfa
Ferfa

Reputation: 221

How to generate sheet for each specific values

I include some code in my project and I have something I do not understand. My With loop doesn't works.

My goal is to create new sheet from a specific cell B16=House and other new sheet for each cells contains PRIVATE word.

Example: When user click on button: - One new sheet created with title=Value of B16 just after my first sheet (name MyFirstSheet) - Some other sheets created for each cells values contains word PRIVATE, just after the previous sheet.

So the result will be MyFirstSheet, House, Test1PRIVATE, Test2PRIVATE.... 

Sub NewSheetFromTemplate()
Dim SearchRange As Range, c As Range
Dim sht As Worksheet

'New sheet for a specific cell
Sheets("TEMPLATE").Copy After:=Sheets("MyFirstSheet")
ActiveSheet.Name = Sheets("MyFirstSheet").Range("B16").Value

'New sheet for each cell contains PRIVATE
With ThiwWorkbook
   Set SearchRange = ActiveSheet.Range("B16:D70")
   For Each c In SearchRange
      If Right(c.Value, 2) = "PRIVATE" Then
         Sheets("TEMPLATE").Copy After:=Sheets("MyFirstSheet")
         Sheets("MyFirstSheet").Name = c.Value
      End If
   Next c
End With
End Sub

The problem is: My first sheet is well created (so i have MyFirstSheet, House, created) but not others sheet for each cell contains "PRIVATE" Excel say ERROR 1004, and created a sheet in title TEMPLATE (2)

Upvotes: 1

Views: 119

Answers (2)

AAzeeze
AAzeeze

Reputation: 93

Thank you @Fabrizio and @Ralph for your assistance and explanation.

My final code:

Sub NewSheetFromTemplate()
Dim SearchRange As Range, c As Range
Dim sht As Worksheet

'New sheet for each value contain "PRIVATE"
With ThiwWorkbook
   Sheets(1).Select
   Set SearchRange = ActiveSheet.Range("A2:C70")
   For Each c In SearchRange
      If Right(c.Value, 7) = "PRIVATE" Then
        Sheets("TEMPLATE").Copy After:=ActiveSheet
        ActiveSheet.Name = c.Value
      End If
   Next c
End With

'New sheet for a specific cell: A2
Sheets(1).Select
Sheets("TEMPLATE").Copy After:=ActiveSheet
ActiveSheet.Name = Sheets(1).Range("A2").Value

'Show OK message
Sheets(1).Select
MsgBox "OK, all sheets well created. Please fill out next sheet"

End Sub

Upvotes: 0

Ralph
Ralph

Reputation: 9444

If I understand the question correctly then you merely need to change the line

If Right(c.Value, 2) = "PRIVATE" Then

to

If UCase(Right(c.Value, 7)) = "PRIVATE" Then

That's because the length of the word "private" is 7 characters and not 2. Furthermore, I am using UCASE to ensure that it will also find a match if private is written with different caps.

Upvotes: 2

Related Questions