| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rename & hide worksheet problem
Hi All
I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
Hi
Not working isn't a very helpful description of the problem but looking at the code you are testing the leftmost 20 characters and then when you find that sheet you are making an assumption there are only 20 characters in the sheet name. Try this Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then sh.Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then MsgBox Flag 'Call RevisedMBA End If End Sub Mike "BeSmart" wrote: Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
For Each sh In Worksheets
If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x If left$(lcase(sh.name,20) = Lcase("Booking Form Revised") then sh.name = "Old BKF chgd " & x Of course this will only work with one such named sheet, you won't be able to name a second sheet as "Old BKF chgd " & x. If you expect to rename multiple sheets you will need a bit more. How about sh.name = Replace(sh.name, "Booking Form Revised", "Old BKF chgd", , , vbTextCompare) & x Regards, Peter T "BeSmart" wrote in message ... Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
Hi Mike H
My apologises for being a novice and not explaining my problem better. and thanks for your revised code - it worked fantastic in a test workbook. But when I put it back into my live workbook and it "called RevisedMBA" the following error message appeared near the beginning of the second macro - when it tried to rename a the worksheet called "Booking Form" to be named "Booking Form Revised" "Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a reference object library or a workbook referenced by Visual Basic When I hit debug the error occurs in the below position: .... Sheets("Booking Form").Select Sheets("Booking Form").Copy Befo=Sheets(3) Sheets("Booking Form (2)").Select Sheets("Booking Form (2)").Name = "Booking Form Revised" <<<error Sheets("Booking Form Revised").Activate .... I want to rename and hide the previous worksheet called "Booking Form Revised" so that the "MBARevised" macro can run and re-use that worksheet name each time the user wants to create revised worksheets. -- Thank for your help BeSmart "Mike H" wrote: Hi Not working isn't a very helpful description of the problem but looking at the code you are testing the leftmost 20 characters and then when you find that sheet you are making an assumption there are only 20 characters in the sheet name. Try this Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then sh.Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then MsgBox Flag 'Call RevisedMBA End If End Sub Mike "BeSmart" wrote: Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
Thanks Peter
Hopefully I won't be using the worksheet name (Old BKF chgd" & x) again because the "x" converts into todays date and time at the end of the file name. e.g. "Old BKF chgd 12-11-08 2258" so the next time it's used it will apply a different time and possibly date. The "Booking Form Revised" worksheet is created via a prior macro, not by a user. The user clicks a button and the worksheet is created showing revised data (along with other worksheets). I'm hoping that by renaming and hiding the previous worksheet called "Booking Form Revised", that the "MBARevised" macro can then re-use the worksheet name "Booking Form Revised" next time the user clicks the "create revised MBA" button however many times. Pls see my reply to Mike H as my problem still exists... -- Thank for your help BeSmart "Peter T" wrote: For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x If left$(lcase(sh.name,20) = Lcase("Booking Form Revised") then sh.name = "Old BKF chgd " & x Of course this will only work with one such named sheet, you won't be able to name a second sheet as "Old BKF chgd " & x. If you expect to rename multiple sheets you will need a bit more. How about sh.name = Replace(sh.name, "Booking Form Revised", "Old BKF chgd", , , vbTextCompare) & x Regards, Peter T "BeSmart" wrote in message ... Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
Hi,
You found 2 sheets the met your criteria and tried to give them both the same sheetname which isn't allowed. Every time you do your hide increment a variable (say) y by 1 and add this to your sheetname with sh.Name = "Old BKF chgd " & x & y y=y+1 Mike Mike "BeSmart" wrote: Hi Mike H My apologises for being a novice and not explaining my problem better. and thanks for your revised code - it worked fantastic in a test workbook. But when I put it back into my live workbook and it "called RevisedMBA" the following error message appeared near the beginning of the second macro - when it tried to rename a the worksheet called "Booking Form" to be named "Booking Form Revised" "Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a reference object library or a workbook referenced by Visual Basic When I hit debug the error occurs in the below position: .... Sheets("Booking Form").Select Sheets("Booking Form").Copy Befo=Sheets(3) Sheets("Booking Form (2)").Select Sheets("Booking Form (2)").Name = "Booking Form Revised" <<<error Sheets("Booking Form Revised").Activate .... I want to rename and hide the previous worksheet called "Booking Form Revised" so that the "MBARevised" macro can run and re-use that worksheet name each time the user wants to create revised worksheets. -- Thank for your help BeSmart "Mike H" wrote: Hi Not working isn't a very helpful description of the problem but looking at the code you are testing the leftmost 20 characters and then when you find that sheet you are making an assumption there are only 20 characters in the sheet name. Try this Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then sh.Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then MsgBox Flag 'Call RevisedMBA End If End Sub Mike "BeSmart" wrote: Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
Hi Mike
It looks like both macros are being checked for errors before they run them and the check is finding the conflict with the sheet names (in the second macro) before it runs the first macro. But if the first macro runs - it which will solve the problem? Or is that not possible??? Yes - I thought about adding a variable at the end of the sheetname. However my code references the latest "Booking Form Revised" worksheet later when it's running. I don't know how to write the code so the macro picks the latest "Booking Form Revised" sheetname with the variable at the end of the name?? -- Thank for your help BeSmart "Mike H" wrote: Hi, You found 2 sheets the met your criteria and tried to give them both the same sheetname which isn't allowed. Every time you do your hide increment a variable (say) y by 1 and add this to your sheetname with sh.Name = "Old BKF chgd " & x & y y=y+1 Mike Mike "BeSmart" wrote: Hi Mike H My apologises for being a novice and not explaining my problem better. and thanks for your revised code - it worked fantastic in a test workbook. But when I put it back into my live workbook and it "called RevisedMBA" the following error message appeared near the beginning of the second macro - when it tried to rename a the worksheet called "Booking Form" to be named "Booking Form Revised" "Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a reference object library or a workbook referenced by Visual Basic When I hit debug the error occurs in the below position: .... Sheets("Booking Form").Select Sheets("Booking Form").Copy Befo=Sheets(3) Sheets("Booking Form (2)").Select Sheets("Booking Form (2)").Name = "Booking Form Revised" <<<error Sheets("Booking Form Revised").Activate .... I want to rename and hide the previous worksheet called "Booking Form Revised" so that the "MBARevised" macro can run and re-use that worksheet name each time the user wants to create revised worksheets. -- Thank for your help BeSmart "Mike H" wrote: Hi Not working isn't a very helpful description of the problem but looking at the code you are testing the leftmost 20 characters and then when you find that sheet you are making an assumption there are only 20 characters in the sheet name. Try this Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then sh.Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then MsgBox Flag 'Call RevisedMBA End If End Sub Mike "BeSmart" wrote: Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
rename & hide worksheet problem
I worked it out...
I moved the renaming actions into the very end of the macro that is run directly before this one (via a different button). The Booking Form Revised sheet is renamed and hidden. Now when the RevisedMBA code runs, the worksheet has already been renamed and I just unhide it - without conflicting with any other worksheet names. -- Thank for your help BeSmart "BeSmart" wrote: Hi Mike It looks like both macros are being checked for errors before they run them and the check is finding the conflict with the sheet names (in the second macro) before it runs the first macro. But if the first macro runs - it which will solve the problem? Or is that not possible??? Yes - I thought about adding a variable at the end of the sheetname. However my code references the latest "Booking Form Revised" worksheet later when it's running. I don't know how to write the code so the macro picks the latest "Booking Form Revised" sheetname with the variable at the end of the name?? -- Thank for your help BeSmart "Mike H" wrote: Hi, You found 2 sheets the met your criteria and tried to give them both the same sheetname which isn't allowed. Every time you do your hide increment a variable (say) y by 1 and add this to your sheetname with sh.Name = "Old BKF chgd " & x & y y=y+1 Mike Mike "BeSmart" wrote: Hi Mike H My apologises for being a novice and not explaining my problem better. and thanks for your revised code - it worked fantastic in a test workbook. But when I put it back into my live workbook and it "called RevisedMBA" the following error message appeared near the beginning of the second macro - when it tried to rename a the worksheet called "Booking Form" to be named "Booking Form Revised" "Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a reference object library or a workbook referenced by Visual Basic When I hit debug the error occurs in the below position: .... Sheets("Booking Form").Select Sheets("Booking Form").Copy Befo=Sheets(3) Sheets("Booking Form (2)").Select Sheets("Booking Form (2)").Name = "Booking Form Revised" <<<error Sheets("Booking Form Revised").Activate .... I want to rename and hide the previous worksheet called "Booking Form Revised" so that the "MBARevised" macro can run and re-use that worksheet name each time the user wants to create revised worksheets. -- Thank for your help BeSmart "Mike H" wrote: Hi Not working isn't a very helpful description of the problem but looking at the code you are testing the leftmost 20 characters and then when you find that sheet you are making an assumption there are only 20 characters in the sheet name. Try this Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then sh.Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then MsgBox Flag 'Call RevisedMBA End If End Sub Mike "BeSmart" wrote: Hi All I can't work out why my code is not working here? I need to find a worksheet, rename and hide it before I can run the next code... The code needs to: - Find any worksheet names starting from the left with the following 20 characters: "booking form revised" (any hidden sheets can be ignored) - If it doesn't find any worksheets with this name then skip the rest of the macro and call "RevisedMBA" - If it does find a worksheet - Rename that worksheet to: "Old BKF chgd " & x (x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") I know that I'm limited to 31 characters in a worksheet name - I've kept under that - just with 29. - Hide the renamed "Old BKF chgd " & x worksheet - Now call RevisedMBA. Current Code: _________________________________ Sub Checksheets() Dim Flag As Integer x = WorksheetFunction.Text(Now(), "dd-mm-yy at hhmm") Flag = 0 For Each sh In Worksheets If Left(sh.Name, 20) = "Booking Form Revised" Then Sheets("Booking Form Revised").Name = "Old BKF chgd " & x Flag = Flag + 1 sh.Visible = xlSheetHidden End If Next If Flag = 0 Then Call RevisedMBA End If End Sub _____________________ -- Thank for your help BeSmart |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rename the new worksheet | Excel Programming | |||
| problem after rename of file | Excel Discussion (Misc queries) | |||
| Copy worksheet, rename, merged cell problem?? | Excel Programming | |||
| Using information from one worksheet, to rename inserted worksheet | Excel Worksheet Functions | |||
| HOW DO I DELETE A WORKSHEET IN EXCEL? NOT HIDE IT OR RENAME IT | Excel Worksheet Functions | |||
Linear Mode
