Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying sheets
My spreadsheet makes multiple copies of a base sheet and then modifies the
new sheets in different ways. The macro works great the first time I open the file. If I try to run it again, it fails. The code is listed below: NumberOfCategories = Sheets("EditCategories").Cells(1, 4) - 1 '(D1-1) CategoryColumn = 5 TargetSheet = 13 LastColumn = CategoryColumn + NumberOfCategories Do Sheets(TargetSheet).Select 'THE CODE GENERALLY FAILS ON THE LINE BELOW WITH TargetSheet=14 Sheets(TargetSheet).Copy After:=Sheets(TargetSheet) DoEvents TargetSheet = TargetSheet + 1 Sheets(TargetSheet).Select NameOfSheet = Worksheets(TargetSheet).Cells(3, CategoryColumn) Sheets(TargetSheet).Name = NameOfSheet CategoryColumn = CategoryColumn + 1 Loop Until CategoryColumn = LastColumn Any help is appreciated, Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying sheets
You will run into a problem if you try to rename a sheet to an mane that is
already in use. You need to test if the sheet name exists prior to attempting the rename... Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function -- HTH... Jim Thomlinson "ZipCurs" wrote: My spreadsheet makes multiple copies of a base sheet and then modifies the new sheets in different ways. The macro works great the first time I open the file. If I try to run it again, it fails. The code is listed below: NumberOfCategories = Sheets("EditCategories").Cells(1, 4) - 1 '(D1-1) CategoryColumn = 5 TargetSheet = 13 LastColumn = CategoryColumn + NumberOfCategories Do Sheets(TargetSheet).Select 'THE CODE GENERALLY FAILS ON THE LINE BELOW WITH TargetSheet=14 Sheets(TargetSheet).Copy After:=Sheets(TargetSheet) DoEvents TargetSheet = TargetSheet + 1 Sheets(TargetSheet).Select NameOfSheet = Worksheets(TargetSheet).Cells(3, CategoryColumn) Sheets(TargetSheet).Name = NameOfSheet CategoryColumn = CategoryColumn + 1 Loop Until CategoryColumn = LastColumn Any help is appreciated, Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying sheets
Jim, Thanks for the response. I do not believe that the problem is with
redundant names. Once the code fails, if I exit and save, and then come back in, the code runs fine. Any other thoughts are welcome. "Jim Thomlinson" wrote: You will run into a problem if you try to rename a sheet to an mane that is already in use. You need to test if the sheet name exists prior to attempting the rename... Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function -- HTH... Jim Thomlinson "ZipCurs" wrote: My spreadsheet makes multiple copies of a base sheet and then modifies the new sheets in different ways. The macro works great the first time I open the file. If I try to run it again, it fails. The code is listed below: NumberOfCategories = Sheets("EditCategories").Cells(1, 4) - 1 '(D1-1) CategoryColumn = 5 TargetSheet = 13 LastColumn = CategoryColumn + NumberOfCategories Do Sheets(TargetSheet).Select 'THE CODE GENERALLY FAILS ON THE LINE BELOW WITH TargetSheet=14 Sheets(TargetSheet).Copy After:=Sheets(TargetSheet) DoEvents TargetSheet = TargetSheet + 1 Sheets(TargetSheet).Select NameOfSheet = Worksheets(TargetSheet).Cells(3, CategoryColumn) Sheets(TargetSheet).Name = NameOfSheet CategoryColumn = CategoryColumn + 1 Loop Until CategoryColumn = LastColumn Any help is appreciated, Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying sheets
Further investigation reveals that the copy sheet function has simply
stopped. Closing and restarting Excel apparently restores the feature. Does this help in suggesting a solution? "Jim Thomlinson" wrote: You will run into a problem if you try to rename a sheet to an mane that is already in use. You need to test if the sheet name exists prior to attempting the rename... Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function -- HTH... Jim Thomlinson "ZipCurs" wrote: My spreadsheet makes multiple copies of a base sheet and then modifies the new sheets in different ways. The macro works great the first time I open the file. If I try to run it again, it fails. The code is listed below: NumberOfCategories = Sheets("EditCategories").Cells(1, 4) - 1 '(D1-1) CategoryColumn = 5 TargetSheet = 13 LastColumn = CategoryColumn + NumberOfCategories Do Sheets(TargetSheet).Select 'THE CODE GENERALLY FAILS ON THE LINE BELOW WITH TargetSheet=14 Sheets(TargetSheet).Copy After:=Sheets(TargetSheet) DoEvents TargetSheet = TargetSheet + 1 Sheets(TargetSheet).Select NameOfSheet = Worksheets(TargetSheet).Cells(3, CategoryColumn) Sheets(TargetSheet).Name = NameOfSheet CategoryColumn = CategoryColumn + 1 Loop Until CategoryColumn = LastColumn Any help is appreciated, Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Sheets | Excel Worksheet Functions | |||
Copying Sheets | Excel Discussion (Misc queries) | |||
Problem with copying sheets | Excel Discussion (Misc queries) | |||
Excel 2000 problem copying drawingobjects between sheets | Excel Discussion (Misc queries) | |||
Problem copying range and pasting to multiple sheets | Excel Programming |