Want to copy and paste with macros
Guido van Duijnhoven
I have a question concerning macros. I have a workbook (single sheet)
wich contains macros. I want to copy and paste the sheet from that
workbook to another, and copy the macros too.
The other workbook is an excisting workbook. I want to paste to an empty
sheet in that workbook.
Is there a simple way to do this?
Thanks in advance.
Regards, Guido
--
For email: change 'nospam' to 'miles'
Tom Ogilvy
If the macros are in the code module associated with the sheet (in
otherwords, if you right click on the sheet tab and do view code, you see
the macros) then just copying the sheet should do it
Edit=>Move or Copy Sheet, select the make a copy option in the lower left
corner and designate the destination.
Regards,
Tom Ogilvy
MVP Excel
"Guido van Duijnhoven" <guido.van....@nospam.nl> wrote in message
news:39A2581D...@nospam.nl...
Guido van Duijnhoven
Thank you for your response, but it's not working. This is what is happening:
When I look in 'view code', I see the macros in my sheet.
Then I make a copy as you told me, do 'view code' in the copied sheet, and see
that the macros are there, and working!
Then I close the original file, and the macros in my copy are gone!
When I open the original file again, the macros are present and can be used.
It looks more like a link instead of a copy.
Guido
Tom Ogilvy
I have never observed the behavior you describe and I can't reproduce it.
The procedures I described work for me - I can't say why you are getting
this strange behavior. If your copy of the sheet has no code in it, then
you can try reopening the original file, going to the code module for the
sheet, select all the code, doing and edit copy, then go to the new sheet
and code module, and paste in the code.
Make sure, when you say the code has disappeared, that you are not scrolled
over to the right or below the code and just can't see it. It happens to me
frequently and is disconcerting until I notice it (just a possibility).
Regards,
Tom Ogilvy
MVP Excel
"Guido van Duijnhoven" <guido.van....@nospam.nl> wrote in message
news:39A27431...@nospam.nl...
Dave Peterson
Are you sure your macros were in at the sheet level? Or was it in a Module?
Did it look like
microsoft Excel objects
sheet1
or more like
modules
module1
Just a swag????
--
Dave Peterson
pete...@netzero.net
Guido van Duijnhoven
I think Dave is wright. Macros are at module level. Under 'view code' I see:
1: filename.xls - sheet 1 (code)
2: filename.xls - module 1 (code) ; my macros appear here!
The next question is: how do I get the macros at the sheet level. When recording a
new macro I can only choose between: This Workbook, New Workbook or Personal Macro
Workbook. I think neither of them will place my macro at the sheet level, or does
it?
Guido
Dave Peterson
When you record a macro, it'll be put into a general module of whichever
workbook you choose (personal/new workbook/this workbook)
The macros on the sheet level are for sheet events (worksheet_selectionchange
for example). These are limited in scope to that particular worksheet.
Take a look at help for worksheet object events (xl97sr2 at least).
Activate
BeforeDoubleClick
BeforeRightClick
Calculate
ChangeDeactivate
SelectionChange
But to copy the module level macros, you could look at Chip Pearson's site:
to pick up some nice code and examples.
You'll export the module to a text file and then import it to the new workbook.
Good luck,
--
Dave Peterson
pete...@netzero.net