Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-31-2015, 10:51 AM
Dritan Dritan is offline Insert Sheet via cell value Windows Vista Insert Sheet via cell value Office 2007
Novice
Insert Sheet via cell value
 
Join Date: Jan 2015
Location: Albania
Posts: 6
Dritan is on a distinguished road
Default Insert Sheet via cell value

I want to use a code that allowed me to insert e new sheet
base on the value of the cell, or the content of the cell.

Any help, pls?
Reply With Quote
  #2  
Old 02-07-2015, 10:53 PM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

If you want to add a worksheet and then give it the name of the active cell the code below will do that.

Code:
Sub CreateNamedWorksheet()
  Dim wb As Workbook, nws As Worksheet, NewName As String
  Dim ws As Worksheet, cws As Worksheet
  
  Set wb = ThisWorkbook
  Set cws = wb.ActiveSheet
  If ActiveCell.Value <> "" Then
    NewName = ActiveCell.Value
    For Each ws In wb.Worksheets
      If ws.Name = NewName Then
        MsgBox ("The name " & NewName & " is already in use. No new worksheet added.")
        End
      End If
    Next ws
    Set nws = wb.Worksheets.Add
    nws.Name = NewName
  Else
    MsgBox "The active cell has no value to name the new worksheet"
    End
  End If
  cws.Activate  'Use a ' right before this line to select the new worksheet.
 
End Sub
Reply With Quote
  #3  
Old 02-08-2015, 09:33 AM
Dritan Dritan is offline Insert Sheet via cell value Windows Vista Insert Sheet via cell value Office 2007
Novice
Insert Sheet via cell value
 
Join Date: Jan 2015
Location: Albania
Posts: 6
Dritan is on a distinguished road
Default

Thank you for the answer,
But it didn't fulfill my need.
I would like within a range cell any of data entered a new worksheet is entered into the workbook.

Thx
Reply With Quote
  #4  
Old 02-08-2015, 10:07 AM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Ok I partly understand. You want a code that looks at a range of cells. If any of these values are changed then you want a worksheet to be added?

If this is true a couple of things need to be identified.

What is the maximum amount of worksheets you want possible in the workbook?
What range do you want to check? Something like A2:A10?
Do you want the new worksheet to be selected after if is created or stay on the orignal worksheet?

Please post back the answers to these questions and I should be able to put something together.
Reply With Quote
  #5  
Old 02-08-2015, 11:44 AM
Dritan Dritan is offline Insert Sheet via cell value Windows Vista Insert Sheet via cell value Office 2007
Novice
Insert Sheet via cell value
 
Join Date: Jan 2015
Location: Albania
Posts: 6
Dritan is on a distinguished road
Default

That one is exactly what i want,

The maximum amount of the worksheet it will be 50
Range will be like D550 (And a version of code if the range is particular like ; D2540: D4560)
I just want to continue in my current sheet (I don't want the new worksheet to be selected).

In the range D550 I will set up data validation so when i put a value in the cell
i want automatically the worksheet to be insert into the workbook.

Thank you once more for helping me to fix that.
Dritan
Reply With Quote
  #6  
Old 02-08-2015, 08:26 PM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Ok not too bad. This code will check if the name already exists and will check if there are over 49 worksheets. Right now it has message boxes that come up if one of these stops occur. You can simply place a ' in front of any line of code to comment it out and cause Excel to ignore it.

You will need to paste this entire code into the actual worksheet object in the visual basic editor. Place it in the main worksheet that you will use.

Let me know if you have any questions.

Thanks

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RangePass As Boolean
  If Target.Row > 4 And Target.Row < 51 Then
    If Target.Column = 4 And Target.Value <> "" Then CreateNewWorksheet (Target.Value)
  End If
  
End Sub
Sub CreateNewWorksheet(UseValue As String)
  Dim wb As Workbook, nws As Worksheet, NewName As String
  Dim ws As Worksheet, cws As Worksheet
  
  Set wb = ThisWorkbook
  Set cws = wb.ActiveSheet
  NewName = UseValue
  If wb.Worksheets.Count > 49 Then
    MsgBox "This workbook can only contain 50 worksheets."
    'If you dont want the messagebox to come up just comment it out with a '
    End
  End If
  For Each ws In wb.Worksheets
    If ws.Name = NewName Then
      MsgBox ("The name " & NewName & " is already in use. No new worksheet added.")
      'You can comment out the message box if you just want code to end.
      End
    End If
  Next ws

  'All passed add the new worksheet
  Set nws = wb.Worksheets.Add
  nws.Name = NewName
  cws.Activate  'Use a ' right before this line to select the new worksheet.
 
End Sub
Reply With Quote
  #7  
Old 02-08-2015, 08:32 PM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

One other thing while it is on my mind. In the event that you do have over 49 worksheets. simply deleting a value will not remove that worksheet. We would need to write a few more lines of code for that. Let me know if that is needed or not. If it is I will need to know what worksheets in the workbook to not delete since I would write a loop to check all the values in D5 to D50 checking if there is a worksheet with the name or not. If there is a workbook name not in that list it would delete the worksheet. This can bring lots of problems though since when you delete a worksheet you cannot get the data back We could always write a backup program to pull all the data off that worksheet and store it in a temporary location incase in does get deleted. Hopefully we do not need to go that route since it will be a lot of work.

Let me know.
Reply With Quote
  #8  
Old 02-09-2015, 05:10 AM
Dritan Dritan is offline Insert Sheet via cell value Windows Vista Insert Sheet via cell value Office 2007
Novice
Insert Sheet via cell value
 
Join Date: Jan 2015
Location: Albania
Posts: 6
Dritan is on a distinguished road
Default

Hi,
It will be perfect if you write a code like that(if the value in the cell is deleted the new worksheet will be deleted automatically), because it will make the workbook dynamic.

I can protect then the cells via password protected so the information will be safe.

thanks a lot for you help, I really appreciate
Reply With Quote
  #9  
Old 02-09-2015, 06:21 PM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Ok I can do this but like I mentioned before I need to know what worksheets to ignore. The reason is because The code will look through every worksheet name and if that name is not in the D5 to D50 list it will get deleted. Now if the only worksheet left to spare is the worksheet where you enter data into d5 to d50 then that's really easy however If it is not then you will run into a problem.

Let me know which worksheet names to spare (Data validation will not keep them from getting deleted.) or if I just need to spare the main worksheet.

Thanks
Reply With Quote
  #10  
Old 02-10-2015, 04:37 AM
Dritan Dritan is offline Insert Sheet via cell value Windows Vista Insert Sheet via cell value Office 2007
Novice
Insert Sheet via cell value
 
Join Date: Jan 2015
Location: Albania
Posts: 6
Dritan is on a distinguished road
Default

hi,
Yes the worksheet which das data (D550) need to be ignore (or a static sheet), the rest of the sheet can be dynamic.

Thank you
Reply With Quote
  #11  
Old 02-10-2015, 11:30 PM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Ok here we go. I put in some additional checks since worksheet names can not be over 31 characters or contain certain special characters. Please ensure that you 100% back up your work on all open excel workbooks when running this. It will delete all worksheets that are not in the range and it will not give a prompt before doing this.

Same instructions as above. Let me know if you have any questions.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RangePass As Boolean
  If Target.Count > 1 Then
    CreateNewWorksheet ("")
    Exit Sub
  End If
  If Target.Row > 4 And Target.Row < 51 Then
    If Target.Column = 4 And Target.Value <> "" Then CreateNewWorksheet (Target.Value)
  End If
  
End Sub
Sub CreateNewWorksheet(UseValue As String)
  Dim wb As Workbook, nws As Worksheet, NewName As String
  Dim ws As Worksheet, cws As Worksheet, CheckString As String
  Dim CheckRow As Integer, CheckName As String, BadData As Variant
  Dim TempString As String, v As Variant
  
  Set wb = ThisWorkbook
  Set cws = wb.ActiveSheet
  BadData = Array("*", "[", "]", "/", "\", "?", "'", ":")
  'Add range names to a string to check
  CheckString = ""
  For CheckRow = 5 To 50
    TempString = Range("D" & CheckRow).Value
    For Each v In BadData
      TempString = Replace(TempString, v, "")
    Next v
    CheckString = CheckString & "[" & TempString & "]"
  Next CheckRow
  CheckString = "[" & cws.Name & "]" & CheckString
  'Delete not found worksheets
  For Each ws In wb.Worksheets
    CheckName = "[" & ws.Name & "]"
    Application.DisplayAlerts = False
    If InStr(1, CheckString, CheckName) = 0 Then
      ws.Delete
    End If
  Next ws
  Application.DisplayAlerts = True
  NewName = UseValue
  'Clear special characters
  For Each v In BadData
    NewName = Replace(NewName, v, "")
  Next v
  If NewName = "" Then End
  If Len(NewName) > 31 Then
    MsgBox "Name too long. no worksheet added."
    End
  End If
  

  If wb.Worksheets.Count > 49 Then
    MsgBox "This workbook can only contain 50 worksheets."
    'If you dont want the messagebox to come up just comment it out with a '
    End
  End If
  For Each ws In wb.Worksheets
    If ws.Name = NewName Then
      MsgBox ("The name " & NewName & " is already in use. No new worksheet added.")
      'You can comment out the message box if you just want code to end.
      End
    End If
  Next ws

  'All passed add the new worksheet
  Set nws = wb.Worksheets.Add(After:=cws)
  nws.Name = NewName
  cws.Activate  'Use a ' right before this line to select the new worksheet.
 
End Sub
Thanks
Reply With Quote
  #12  
Old 02-12-2015, 02:01 PM
Dritan Dritan is offline Insert Sheet via cell value Windows Vista Insert Sheet via cell value Office 2007
Novice
Insert Sheet via cell value
 
Join Date: Jan 2015
Location: Albania
Posts: 6
Dritan is on a distinguished road
Default

Hi,

You have done great job , super super
Now I'll go through and start to understand.

One quick question: Does this code work with any excel/VBA version?
If I have other question may i come back to you again?

Thank you so much for your help

Dritan
Reply With Quote
  #13  
Old 02-12-2015, 09:17 PM
excelledsoftware excelledsoftware is offline Insert Sheet via cell value Windows 7 64bit Insert Sheet via cell value Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Thank you so much for the kind words. The code should work for most Excel versions. I have seen issues with some code running on Macs though. You are more than welcome to reach out to me with any questions.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Sheet via cell value Help Coloring a cell in Sheet one if data is missing from another sheet Aeducan Excel 1 06-22-2014 04:49 PM
Insert Sheet via cell value How to set a hyperlink from a pp presentation into a CELL from an excel sheet Sabi PowerPoint 1 04-30-2013 06:36 AM
Insert Sheet via cell value Insert Excel sheet markg2 Word 1 12-15-2010 12:19 PM
copy cell from sheet 2 to sheet 3 macro slipperyjim Excel Programming 1 02-18-2010 01:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft