Pasting via recorded macro problem.

I tried to record a macro copying some text from one workbook's worksheet to another workbook.
If the text happens to have already been copied into memory, it works. But if I copy some other text then try to run the macro, it will paste whatever I've copied before and not the specified cell.

Please Login or Register  to view this content.

I thought maybe adding ActiveSheet.Copy after the line: Range("T8:W8").Select
but then it just pastes the text ActiveSheet.Paste into the cell B5. I want to copy the data only from T8:W8.
Can someone please explain what needs correcting? Does copy/paste work with macros in Excel?

Hi foolios and welcome to the forum. Try the code now with cutcopymode equaling false.

Please Login or Register  to view this content.

Can you show me where you looked up cutcopymode? I am trying to find ways of working with excel at: http://msdn.microsoft.com/en-us/library/ff846392.aspx
the reference library for Excel, but it is very difficult to wade thru.

By the way, I get error 1004
Paste method of worksheet failed.

When I hit debug, error at line:
ActiveSheet.Paste

Only if I have the text Electric already copied to memory will it paste.

P.S. I found it at: http://msdn.microsoft.com/en-us/library/ff839532.aspx
Is that where you go looking for explanations?

Also, is the following correct?

Please Login or Register  to view this content.

I have learned about cutcopymode through this forum. I just Googled it and here is a good webpage to look at. If you are up to learning how to write good macros, keep coming to this site with your problems and queries. A good starting habit to learn is to avoid using .Select and .Activate. Using them tends to slow the Run-Time dramatically, especially with large procedures. Give me a moment and I'll post a short procedure without .Select or Activate.

I was going to try and work with your code but I really don't know what you are trying to accomplish with it. Sorry, perhaps my mind is not doing what it should be doing. Are you wanting to copy the range of cells T8:W8 into B5?

Thanks so much in advance!


It should have read: Windows("COPY_B.csv").Activate

I added: Selection.Copy after

Range("T8:W8").Select
Selection.Copy

And now it does paste well but it also pastes the formatting of the cell of which I don't want copied.

EDIT:
Ok, I found out that the problem was in that the macro was in the same workbook. So, I moved the macro to my personal workbook for macros.
It works perfectly when run as a macro.
But now the problem is that when I try to assign the macro to a button, it errors out.
Sub or function not defined.
When I had the macro in the same workbook I could use Macro2 in the sub but now that isn't working.
So I tried PERSONAL.XLSB!Macro2 and that doesn't work either.
How do I call the macro now?

Private Sub CommandButton1_Click()
Macro2
End Sub

Can you upload a mock workbook with data that is not sensitive or private? This way I can look at where you problems may be.

See if this somehow fits what you are trying to accomplish.

Please Login or Register  to view this content.

Hi foolios,
See the attached workbooks. The button will take data from book 5 and bring it to book 6. However, the data coming from book 5 is based on your range T8:W8 (or T8:AXY8, the column count doesn't matter). Look over the code and fit it to your needs. If you need help, ask the forum.

Cheers

Hi foolios,

I'll take a look at your workbooks in a moment.

Debug.Print can be commented out or deleted. What it does is enables you to view Range address, values, and and other goodies in the Immediate window the the VBE. The Immediate window is at the bottom of the VBE screen. If not enabled on your screen, press Ctrl + G and it will show up. It is very useful to use before placing values in the spreadsheet. However, once your code is doing what you want it to do, you should comment them out or delete them because they will still print in your Immediate window, even if your VBE is closed. Hopefully this makes sense!

As for another question, if it is different than this one, mark this as solved and then start a new thread. It helps with regards to search engines.

Hi foolios, I have been having problems with saving you files to actually run the code. I looked at the code and I think everything looks good. If it meets your requirements then I would say that you are good to go. Let me know if there is anything else.

PS. Don't forget to delete or comment out the debugs.

Michael