Code to cut cell from one workbook, close same workbook, and paste in different workbook

Thanks but how do I incorporate your code into mine?

Thanks for your response.
I'm not sure why but when the code gets up to the part of pasting the cut cells there is nothing on the clipboard to be pasted. Looks like once the code saves the workbook all data on the clipboard is erased if we use 'cut and paste', however, the code will work if we use 'copy and paste' but I am trying to avoid copy and paste. Any other suggestions?

Thanks!

Here is the full code.
Let me know if you want me to explain in detail what the code does.
Please note that the active workbook is called "CUSTOMER DATABASE.xlsm"

Please Login or Register  to view this content.

I'm having issues with highlighted line.

Please Login or Register  to view this content.

Hi xladept. Thanks again for your response. it seems that we are back to square one, the same 2 rows got highlighted by excel - see font in red.

Hope to hear from you again

Please Login or Register  to view this content.

How about going a different direction.
I tried recording the process but it didn't work.
Here is what I changed, instead of cutting I will now resort to copying, however the code is still not working. Can you see if you can modify it for me?

Please Login or Register  to view this content.

Thanks.

Originally Posted by kosherboy

How about going a different direction.
I tried recording the process but it didn't work.
Here is what I changed, instead of cutting I will now resort to copying, however the code is still not working. Can you see if you can modify it for me?

Please Login or Register  to view this content.

Thanks.

I'm wondering why you are activating a Windows object rather than the specific worksheet.
You have to activate the window to do things like remove gridlines or set the zoom level, but for specificity in moving data, you need
to direct things to the particular worksheet(s) involved.

Because i'm working with 3 different workbooks and I thought the only way to select it is by activating it?

If I recall correctly, you don't need to actually select or activate in order to use either the Copy method with a target parameter, or the PasteSpecial Method.

But you do need to provide specificity.

Something like this:

Please Login or Register  to view this content.

You can keep all workbooks open while doing this (which speeds it up a bit) and close them afterward.
The main thing is to understand your PasteSpecial parameters (so you don't end up transferring format info or
formulas, if you don't want them) and to be very specific about where you want to grab data from and where
you want it to go. You can do it without using "Select" or "Activate", and you can also turn Screenupdating" off,
using:

Please Login or Register  to view this content.

Nice response and this does explain a lot to me in other aspects, however, I really need the code to close the workbook from where the copied cell is coming from. Keep in mind (as I have originally posted) that the full code creates, saves and names workbooks and sometimes I use the code to create 20 at a shot therefore it's imperative that the code copies the cell from the created workbook and closes the workbook right away. You see right now when I use the code i'm left with many open workbooks that I have to manually close.

Well, it looks like you looked into it while in debug and also noted the clipboard was empty after you did the close.

In that case, I'd recommend using an intermediate store for the info. So copy the info, stash it in an intermediate store, and then close the workbook.

If you only need the value, then just do an assignment to a local var. If you need all of the cell's info (fomula, text, formatting, etc) then you should paste it into
a specific range in ThisWorkbook (a reserved Object, referring to the workbook running the code). After doing the Copy, that would look like:

Please Login or Register  to view this content.

So in a loop, you could open or create each workbook, find the location you want to Copy From, and do the PasteSpecial to ThisWorkbook, then Close the open workbook you want closed

You'd grab a copy from that intermediate location just before pasting it wherever you want it to go.

In that loop it would be a good idea to do a Clear on the range you use as an intermediate store, before putting the next copy there.

By the way, I don't see any problem with having twenty open workbooks. I have a processing system that requires a minimum of six open workbooks (fairly big ones) just to operate, and I process several other workbooks all at once (five or more) on top of that. It's only a problem if the workbooks in question are really huge.

Originally Posted by Quixologies

By the way, I don't see any problem with having twenty open workbooks. I have a processing system that requires a minimum of six open workbooks (fairly big ones) just to operate, and I process several other workbooks all at once (five or more) on top of that. It's only a problem if the workbooks in question are really huge.

You're right, but I have already my main open workbooks (which add up to about 5 or 6) but the many invoices and sales order and pro-formas I create there is no need for them to be open.

Originally Posted by Quixologies

Well, it looks like you looked into it while in debug and also noted the clipboard was empty after you......

Unfortunately I am not yet familiar with looping. Can you incorporate your response into the code?

Here is the full code. I inserted a note for you and that is where I would like to insert the part where the workbook should close.

Please Login or Register  to view this content.

When you stated you have to have several files open at once, I assumed you'd need a loop to go through them all.
This looks like you are really only processing them one at a time, based on a button click.

You have several minor problems I can't take time to address, but it starts with reading what I wrote about activating a "window" object, versus getting more specific with your worksheet identifiers. Relying on a window object is not as direct as you need to make sure you can rely on the code (Workbooks have Windows, not the other way around.)

But right where you highlighted the code, you should insert the PasteSpecial to ThisWorkbook that I showed you, but use a Clear on the range first.

Then you can close your workbook like you planned. When you are ready to use that data again, you have to do another Copy method to get it from
ThisWorkbook.

I see no reason why you have to close the workbook before finishing your Copy/PasteSpecial, however. Unless I'm missing something, you can close it after you've finished with that part of the code. In that case, you shouldn't need to store the cell in ThisWorkbook, and your Clipboard should still have data when you need it.

And by the way, the clipboard behavior in Excel differs from other Microsoft Applications.
Here's an article describing why

That is why using an intermediate store is sometimes necessary.

One other thing: I used the word "specificity". The usual term is "fully qualified". Try searching the help for "Avoiding Naming Conflicts" or "fully qualified range" to see what I mean.

Sorry I can't do more!

Can you please incorporate it into the full code?
Sorry I keep asking but i'm still new to VBA and a lot of your VBA scripts are brand new to me.

Good news and bad news.The good news is that the code closed the workbook but the bad news is it didn't complete the rest of my code by adding the hyperlink as is the purpose of the code.

I realized and I corrected the issues but it still didn't work.

Hi xladept. To be honest, it would take a long time to simplify all the workbooks I have and attach them. I would have to make numerous adjustments as the workbooks are programmed to work in my office with our network if you know what I mean. And of course there is a lot of personal company info as well.
Anyway, I played around a bit with the code (and I also resorted to a different alternative which I came to terms with) so here is my code which takes care of every issue.

Please Login or Register  to view this content.

It's true that when I originally posted this problem I wanted the cell to be cut out of the workbook entirely (so the workbook will look clean to the customer)
You're probably going to ask "so how do you 'clean up' your workbook from the cell that you originally wanted to cut out?" Well, that's simple and I don't know why I didn't think of it in the beginning. I have several buttons on the (newly created) workbook that I use when processing the Pro-Forma Invoice, so basically all I have to do is make one of the buttons clear the contents of the cell that I don't want!

To conclude, I am very appreciative to you for taking your time in helping me out. I learnt a lot of new stuff in VBA from your posts

Take care and best of luck to you