fixing problem

R

radio848

a few days ago, someone provided me with this macro:
Sub SM()
Application.ScreenUpdating = False
Dim CurrentSheet As String
CurrentSheet = Application.ActiveSheet.Name

Sheets(CurrentSheet).Copy After:=Sheets(CurrentSheet)
End Sub

it works fine. copies the sheet and everything. but, what do i add and
where, to clear the macro in the copied page after it has done its job.
in other words, to be active in the first sheet, (control) if you will
sheet.

help!

Thanks
 
R

radio848

Please help:
Here is a sample of this function as it is used in Lotus123. (sorry) but it
works

Sub Sm4
[A5].Select
[].NewSheet $After,1,True
[%Sheet_Count].Contents = [%Sheet_Count].CellValue + 1
[].SheetName = [T_17].CellValue + " " + Cstr([%Sheet_Count].CellValue)
[].GroupSheets 0,1,0
[Picture 1].IsLocked = 0
[Picture 1].Visible = 0
[A:A1..A:L100].QuickCopy [B:A1]
[Picture 1].Visible = 1
[Picture 1].IsLocked = 1
[].UnGroupSheets
[B:B5].Select
[].HorizontalTitle = 1
[B:B3].DeleteRows $Full
End Sub

Can this be adapted for use in excel? I sure hope so.

Thanks in advance....
 
B

Bill Lunney

Don't understand the last bit of your description. The Lotus snippet
doesn't help but I'll here's what I think you mean.

You don't want the macro appearing with the newly copied sheet?

Well, you could move this code to a public module and execute it there.
Depends on how you're executing this macro (is it via a button or
something?).

Anyway in VBA if you want to do this:

1) Go to the VB editor
2) Right click on your project (project window) and choose Insert > Module
3) Double click the newly created module
4) Insert your code here

The code here will be visible globally through Excel (ie. to all worksheets,
modules etc) and when copying sheets it won't appear.




--
Regards,


Bill Lunney
www.billlunney.com
 
E

Eddy

I believe the macro is not attached to the worksheet. Delete codes from a
module is a complex job. You may have to clarify a bit in order for others
to help.
 
R

radio848

Eddy:
I can do that. In the lotus example, the macro is attached to a picture and
is part of what they call a "Smart Master" Template.
In the other example I have taken the picture for the sm template and
altered the code so that it will work in excel.
But cannot clear the picture in the copied sheet.
Does this explanation help clarify things a bit?

"I first started using spreadsheets with the advent of "Parsons Procalc"
which was a modification of Lotus. Procalc was VERY GOOD. I Created a
spreadsheet in procalc, for my home budget, (which has grown and migrated to
excel 2000. "Parsons Money Counts & Procalc were a pair. and extremely
powerful. If Excel could only do what Procalc would do circa '1992', oh
would I be happy.

As technology grows, so does my spreadsheet for keeping track of our home
budget.

I run both excel and lotus 123 ver 9.6 for different reasons.
This is getting long so i will quit.

Do appreciated the help though. "Greatly"!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top