Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

I thank Garry of this forum for this code which I want to make into a generic scheme of:

Unhide the sheet named "CopyMe"
Make a copy/s and name it using the name/s in Sheets("Sheet1").Range("MyNewList")
Rehide "CopyMe"

What it does after my small alterations is copy a sheet for each name in MyNewList, properly name them from that list, then produce an additional two sheets named CopyMe(2) and CopyMe(3), then errors our on this line of ErrHandler: ActiveSheet.Name = vNames.

If there are no names in the "MtNewList" then it produces CopyMe(2) and CopyMe(3) and errors out on the same line as noted above.

The code is in a standard module and "MyNewList" is Workbook in scope.

Thanks.
Howard

Option Explicit
Option Base 1

Type udtAppModes
Events As Boolean: CalcMode As Long: Display As Boolean: RunFast As Boolean
End Type
Public AppMode As udtAppModes

Sub CopySheetAndNameCopies()
'** COLUMN A SHEET NAMES LIST CANNOT HAVE GAPS ***
Dim vNames, n&

On Error Resume Next '//handles empty list
vNames = Sheets("Sheet1").Range("MyNewList")
If Not IsArray(vNames) Then
If vNames = "" Then Beep: Exit Sub
End If 'Not IsArray
On Error GoTo ErrHandler '//handles only 1 sheetname

EnableFastCode
Sheets("CopyMe").Visible = True

For n = LBound(vNames) To UBound(vNames)
If Not bSheetExists(vNames(n, 1)) Then
Sheets("CopyMe").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = vNames(n, 1)
End If 'Not bSheetExists
Next 'n

NormalExit:
Sheets("CopyMe").Visible = False: Sheets("Sheet1").Select
EnableFastCode False: Exit Sub

ErrHandler:
If Not bSheetExists(vNames) Then
Sheets("CopyMe").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = vNames
End If 'Not bSheetExists
Resume NormalExit
End Sub 'CopySheetAndNameCopies
Function bSheetExists(WksName) As Boolean
On Error Resume Next
bSheetExists = CBool(Len(ActiveWorkbook.Sheets(WksName).Name))
End Function
Public Sub EnableFastCode(Optional SetFast As Boolean = True)
'Make sure we're not already enabled/disabled elsewhere
If AppMode.RunFast = SetFast Then Exit Sub
With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.RunFast = True
Else
.ScreenUpdating = AppMode.Display: .Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events: AppMode.RunFast = False
End If
End With
End Sub

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

Hi Howard,

Am Sat, 12 Oct 2013 03:59:00 -0700 (PDT) schrieb Howard:

Unhide the sheet named "CopyMe"
Make a copy/s and name it using the name/s in Sheets("Sheet1").Range("MyNewList")
Rehide "CopyMe"


try in a standard module:

Function SheetExists(strShName As String) As Boolean
On Error Resume Next
SheetExists = Not Sheets(strShName) Is Nothing
End Function

Sub CopyMe()
Dim rngC As Range
Application.ScreenUpdating = False
With Sheets("CopyMe")
.Visible = True
For Each rngC In .Range("MyNewList")
If Not SheetExists(rngC.Value) Then
.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = rngC
End If
Next
.Visible = False
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

Hallo Howard,

Am Sat, 12 Oct 2013 13:29:41 +0200 schrieb Claus Busch:

For Each rngC In .Range("MyNewList")


Change to:
For Each rngC In Sheets("Sheet1").Range("MyNewList")


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

On Saturday, October 12, 2013 4:44:17 AM UTC-7, Claus Busch wrote:
Hallo Howard,



Am Sat, 12 Oct 2013 13:29:41 +0200 schrieb Claus Busch:



For Each rngC In .Range("MyNewList")




Change to:

For Each rngC In Sheets("Sheet1").Range("MyNewList")


Regards


I'm getting sheets named as they should be PLUS a CopyMe(2) and then an error on line:

ActiveSheet.Name = rngC

I also changed MyNewList to sheet 1 scope. As workbook scope I believe I was getting only a CopyMe(2) then the error.

Howard
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

Hi Howard,

Am Sat, 12 Oct 2013 05:00:41 -0700 (PDT) schrieb Howard:

I'm getting sheets named as they should be PLUS a CopyMe(2) and then an error on line:


do you have an empty cell in MyNewList?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

On Saturday, October 12, 2013 5:03:51 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 12 Oct 2013 05:00:41 -0700 (PDT) schrieb Howard:



I'm getting sheets named as they should be PLUS a CopyMe(2) and then an error on line:




do you have an empty cell in MyNewList?





Regards

Claus B.


Yes, the range of MyNewList is A1:A10, and I have just been doing a couple names in testing.

I tried to reset the range to this but the Define Name Box would not take it.
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

Howard
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

Hi Howard,

Am Sat, 12 Oct 2013 05:22:23 -0700 (PDT) schrieb Howard:

I tried to reset the range to this but the Define Name Box would not take it.
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


try:
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
for MyNewList


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

On Saturday, October 12, 2013 5:35:51 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 12 Oct 2013 05:22:23 -0700 (PDT) schrieb Howard:



I tried to reset the range to this but the Define Name Box would not take it.


Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)




try:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))

for MyNewList





Regards

Claus B.


It errors out on this line.

For Each rngC In Sheets("Sheet1").Range("MyNewList")

This does go in the refers to: of the define name box, right?
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))


Howard
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Unhide sheet, copy and rename new sheets from list, rehide sheet

Hi Howard,

Am Sat, 12 Oct 2013 05:48:56 -0700 (PDT) schrieb Howard:

For Each rngC In Sheets("Sheet1").Range("MyNewList")


for a range name (workbook)

Sub CopyMe()
Dim rngC As Range
Application.ScreenUpdating = False
ActiveWorkbook.Names.Add "MyNewList", _
RefersTo:="=Offset(Sheet1!$A$1,,,CountA(Sheet1!$A: $A))"
With Sheets("CopyMe")
.Visible = True
For Each rngC In Range("MyNewList")
If Not SheetExists(rngC.Value) Then
.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = rngC
End If
Next
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

If you have a range name (workbook scope)
and not all cells are filled try:
Sub CopyMe2()
Dim rngC As Range
Application.ScreenUpdating = False
With Sheets("CopyMe")
.Visible = True
For Each rngC In Range("MyNewList")
If Len(rngC) 0 Then
If Not SheetExists(rngC.Value) Then
.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = rngC
End If
End If
Next
.Visible = False
End With
Application.ScreenUpdating = True
End Sub

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Unhide sheet, copy and rename new sheets from list, rehide sheet


All is well! Thank you, Claus.

Regards,
Howard
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Sheet and rename it. Cimjet[_3_] Excel Programming 11 June 10th 11 08:32 PM
How can I copy sheet, fill in cell from list and rename worksheet? [email protected] Excel Programming 4 July 31st 06 07:44 AM
How to copy a sheet and rename it with the value of two cells from the source sheet? Simon Lloyd[_717_] Excel Programming 0 May 12th 06 01:31 AM
Rehide Sheet in macro. ChuckF Excel Worksheet Functions 3 March 29th 06 10:39 PM
Button to copy sheet, rename sheet sequencially. foxgguy2005[_3_] Excel Programming 9 June 17th 05 01:41 PM


All times are GMT +1. The time now is 11:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"