can't rename copied worksheet, due to its code?

Ken Cowen

Board Regular
Joined
Jan 18, 2015
Messages
217
i have a procedure that groups two worksheets then copies them to a new workbook, then among other things renames one of the worksheets. One worksheet that is copied can not be renamed and it seems to be related to the code on the sheet module. The sheet module has code for the Beforedoubleclick and BeforeRightClick events. Both of those procedures have calls to procedure in a regular module. I have other worksheets that can be copied and subsequently renamed, that also have those same procedures with calls to the same procedures in the regular module. the troublesome worksheet can be copied, but not renamed or even addressed in any way.

This is in the worksheet code module for the worksheet that can not be renamed:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim n As Integer, rw As Integer
Dim k As String, fn As String, lnk As String
Dim db As Range
Dim check As Variant

Static ord As Boolean


'open or switch to item file if item is double-clicked


If Not Intersect(Range("items"), ActiveCell) Is Nothing Then
fn = "ITEM " & Target.Value & ".xlsm"
lnk = ActiveWorkbook.Path & "" & fn
Call SwitchOrOpen(fn, lnk)
Cancel = True
Exit Sub
End If
End Sub

If I comment out the Call SwithOrOpen line it can be renamed. I have retyped the entire module, and it still has the exact same issue. Tthere are several other procedures and everyone that has a call to another procedure has to have the call commented out or the code crashes on renaming the worksheet.

This is the code that copies the worksheets:

Private Sub CommandButton28_Click()


Dim sh As Variant
Dim wb As Workbook
Dim n As Integer, i As Integer, x As Integer
Dim str As String

'copy mhi pricing sheet to new book


Sheets(Array("Material", "MHI Pricing", "Rates")).Copy

Set wb = ActiveWorkbook
Set sh = ActiveSheet

Sheets("Material").Name = "Gov Copy"
Sheets("MHI Pricing").Name = "xxxx"

Exit Sub

i really only want to copy the rates and MHI Pricing worksheets, but I added the Material worksheet because it has nearly the identical sheet module procedures, including BeforeClick and BeforeDoubleClick events, with calls to procedures in a code module. There is only one sheet that doesn't work. I have changed the sequence of listing the sheets to be copied, made other changes to sheets post copying. I can do whatever I want to sheets except for MHI Pricing, and if I delete my calls to the code module from the worksheet code page, I can do what I want with that one too.

Doe anyone have any ideas?

Thanks

Ken
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hia, could you post the SwitchOrOpen code?
Please use code tags (click the # symbol & paste your code between) when you do.
Cheers
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top