+ Reply to Thread
Results 1 to 31 of 31

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

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    Hi,
    I'm trying to write a code that would cut a cell from workbook A, close Workbook A and then paste the cell in workbook B. Sounds simple but the code doesn't work at all.

    I would really appreciate any help on this

    Thanks,

    Please Login or Register  to view this content.
    Last edited by kosherboy; 03-06-2014 at 05:24 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    Thanks but how do I incorporate your code into mine?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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?

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hi Kosherboy,

    Can you post the whole code, if you will then I'll rewrite it for you

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hi Kosherboy,

    I rewrote it through the problem area - see if it works now:

    Please Login or Register  to view this content.
    And, thanks for the rep!
    Last edited by xladept; 03-10-2014 at 05:34 PM.

  9. #9
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    I'm having issues with highlighted line.


    Please Login or Register  to view this content.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    It's a typo - Sorry:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Maybe:

    Please Login or Register  to view this content.
    Or:

    Please Login or Register  to view this content.
    And - Thanks again for the rep again!
    Last edited by xladept; 03-11-2014 at 05:50 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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.

  14. #14
    Registered User
    Join Date
    04-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2000
    Posts
    17

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

    Quote Originally Posted by kosherboy View Post
    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.

  15. #15
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

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

  16. #16
    Registered User
    Join Date
    04-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2000
    Posts
    17

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

    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.

  17. #17
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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.

  18. #18
    Registered User
    Join Date
    04-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2000
    Posts
    17

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

    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.

  19. #19
    Registered User
    Join Date
    04-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2000
    Posts
    17

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

    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.

  20. #20
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    Quote Originally Posted by Quixologies View Post
    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.

  21. #21
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    Quote Originally Posted by Quixologies View Post
    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.

  22. #22
    Registered User
    Join Date
    04-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2000
    Posts
    17

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

    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!

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Maybe:
    Please Login or Register  to view this content.
    And - Thanks for the rep!

  24. #24
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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.

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hi Kosherboy,

    Please Login or Register  to view this content.
    And, thanks for the rep!
    Last edited by xladept; 03-12-2014 at 05:04 PM.

  26. #26
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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.

  27. #27
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hi Kosherboy,

    I copied that typo from my old code - it's fixed now, see if the hyperlink works now

  28. #28
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

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

  29. #29
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hi Kosherboy,

    Attach a sample "Pro-Forma Invoice" workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  30. #30
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

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

    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

  31. #31
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

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

    Hi Kosherboy,

    What great news! All of us are gratified when the original poster resolves his own issue

    And, again! Thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code to copy data from a closed workbook and paste in active workbook using named range.
    By paullie1912 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2014, 02:38 AM
  2. unable to close password protected workbook using VBA code in another workbook
    By abulooz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2012, 08:54 AM
  3. Replies: 2
    Last Post: 09-11-2012, 09:42 AM
  4. [SOLVED] Edit code to close all workbooks except active workbook and other specific workbook
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2012, 09:29 PM
  5. Paste data from active workbook to another workbook using code.
    By 2funny4words in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2009, 04:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1