Macro works once, not twice
 

I created a macro that sits on a worksheet(A), when run, it unhides
worksheet(B) and makes a copy of worksheet(B) prompting the user to
name the new worksheet(C). This works well. Part two of the process
includes a button on worksheet(C) that references the same macro on
worksheet(A). Clicking the button on worksheet(C) gives me a
"run-time error 1004" asking if I would like to debug. Debugging tags
this line in the macro:

Sheets("Earnings Code").Select

Full macro looks like this:

Sub NewEarningsCodeSheet()
Call UnhideEarningsCode
Sheets("Earnings Code").Select
shName = Application.InputBox("Enter The Name of the New Earnings
Code", "Delphia Consulting")
If (shName = False) Or shName = "" Then Exit Sub

Sheets("Earnings Code").Copy After:=Sheets(4)
ActiveSheet.Name = shName


End Sub

What am I doing wrong? It seems like it can only copy the worksheet
once or that it is trying to find it in a different location. I
appreciate any help, as I am somewhat new to this. Fun though.

Macro works once, not twice
 

What's the code for 'UnhideEarningsCode'

--
Message posted from http://www.ExcelForum.com

Macro works once, not twice
 

What's the code for 'UnhideEarningsCode'

--
Message posted from http://www.ExcelForum.com

Macro works once, not twice
 

Sub UnhideEarningsCode()

Sheets("Earnings Code").Visible = True

Worksheets("Earnings Code Setup Document").Activate

End Sub



Sorry I didn't include that before. Thanks.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Macro works once, not twice
 

Sorry code is actually

Sub UnhideEarningsCode()

Sheets("Earnings Code").Visible = True

Worksheets("Earnings Code").Activate

End Sub


Didn't change anything. Macro will run multiple time from the origianl
worksheet, but will not run from the button on the newly created
worksheet.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Macro works once, not twice
 

This isn't a fix; just some information to consider in the meantime
You've use Sheet("Sheetname") to access your different worksheets. yo
should refer to them by their sheet name instead. It makes for a lo
less code to write in the long run. While in the VBA editor, selec
view, then project explorer (if its not already visible. It's th
window in the top-left corner of the window. Under Microsoft Exce
Objects, you'll see each sheet, first, by it's name and then, i
parenthesis, it's sheet number.
i.e. MySheetName (Sheet 1).
When accessing the sheet within your code, you can us
*Sheet1.function* as opposed to *Sheets("MySheetName").function*.

I'll keep storming through a solution to your problem.

-Gitcyphe

--
Message posted from http://www.ExcelForum.com

Macro works once, not twice
 

Thanks for the help. Much appreciated. Got it to work with the
following but it becomes slower each time the macro runs. Since the
macro runs fine the first time from the first worksheet, I modified the
macro to mae that sheet active first and then unhide the other sheet to
copy. It works just not well. Thanks again

Sub NewEarningsCodeSheetCopy()
Worksheets("Table of Contents").Activate
Call UnhideEarningsCode
Sheets("Earnings Code").Select
shName = Application.InputBox("Enter The Name of the New Earnings
Code", "Delphia Consulting")
If (shName = False) Or shName = "" Then Exit Sub

Sheets("Earnings Code").Copy After:=Sheets(4)
ActiveSheet.Name = shName


End Sub



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!