Macro doesn't work on button, but fine from Macro menu!

Hi Folks

I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

Any help would be greatfully recieved!

Cheers
DEA

DrEvilAces,

Please attach your workbook, or a sample of it, with the macro, and the code for the command button.


Have a great day,
Stan

Heres a copy of the document I'm working on, the macro is supposed to create a new document, copied from the Tender Debrief sheet. Then create a new line under the header in Tender Diary and copy the information from the red cells in Tender debrief across to the various columns in Tender diary.

runs fine from macro menu but not from the front page button!

I'd really appreciate any help thats could be offered

Cheers

DEA

PS This is a stripped down version for sample purpose so some tabs are missing, but they are not required for the macro.

This is all you need to copy a sheet to a new workbook

Please Login or Register  to view this content.

Your code does not define the worksheets/workbooks it is using. For example which would be the activecell,l you have previously selected all the cells. There's no way to be sure what you are referring to. The whole code could be made more efficient simply by removing all the unnecessary Selects

Thanks for the help.

I used the code you recommended and now it creates a new work book and pastes 'Sheets("Tender Debrief").Copy' into cell A1!

it then stops at the next copy command.

What I dont understand is that the whole macro works from the run menu, but not when pasted into the button code!

Any other ideas?

I've tried to follow what your coded is doing and amended it

Please Login or Register  to view this content.

Fantastic it (almost) works.

Only a couple of small things though, for some reason cell O14 from Tender Debrief is not copied to D5 in Tender Diary, it seems to copy up the value from below (D6)! I tried changing it from:

.Range("D5").Value = Sheets("Tender Debrief").Range("o14")

to:

Sheets("Tender Debrief").Range("O14").Copy
.Range("D5").pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But that made no difference!

And the auto fill function was supposed to number B5 in Tender Diary, so that each entry had a different entry number.

I've got it pasted into the button script and so that now works, just these couple of bits.

It does but you averwrite D5 with D6 at the end

Please Login or Register  to view this content.

You also want to use Data Validation to limit the choice in o14 to Win, Lost or Ongoing.

You can't use autofill to increment a number

Please Login or Register  to view this content.

You are quite literally THE MAN!!

It works like a dream, all I need to do now is go through it and study the code to understand why it works.

Thank you so much for your time and help
Cheers :-)

Originally Posted by DrEvilAces

You are quite literally THE MAN!!

It works like a dream, all I need to do now is go through it and study the code to understand why it works.

Thank you so much for your time and help
Cheers :-)

Note the absence of selecting, that speeds up the code.

Yeah I did spot that, I've only ever recorded macros before, not actually looked into the code thats it churns out of the other end.
This has been really useful for me and I think it's time to spend a few hours (months) learning more.

Can you recommend any good beginners sites on coding macros to set me off in the right direction?

The macro recorder is a good start, but remember the code generated can generally be improved.

Asking questions & reading here is a good start

See this post

fantastic, thanks for all your help :-)