+ Reply to Thread
Results 1 to 8 of 8

Switch back to previous workbook

  1. #1
    Registered User
    Join Date
    10-30-2007
    Posts
    5

    Switch back to previous workbook

    Hi all
    This probably very easy to do but I can't figure out how.

    I have a macro that unprotects a worksheet copies data from that worksheet and pastes it into a newly created and saved workbook. However I have two problems

    1) I'm attempting to copy the data from the first workbook by selecting all cells and then when switching to the new workbook, select all cells and the paste special (values) then paste special (formats). This works when recording the macro but when I attempt to run it it bombs out.

    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False



    2) How do I get the macro to switch back to the workbook it got its data from. The name of the source workbook varies and there may be from 2 to 15 workbooks open. Is there a previous active workbook command? or do I need to create some kkind of temporary value to hold the file name of the source workbook.


    Regards
    John

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello John,

    Are you looking for an easier way to manually copy the data or to automate the process entirely?

    Thanks.
    Leith Ross

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Something like this:
    Please Login or Register  to view this content.
    Edit: Please edit your post to add code tags.
    Last edited by shg; 11-29-2007 at 04:04 AM.

  4. #4
    Registered User
    Join Date
    10-30-2007
    Posts
    5
    Hi All
    thanks for the replies

    Leith, yes I'm looking at an easier way to copy and paste, but the copy is from a protected worksheet that is highly formulated. I also I don't want the user to be able to unprotect so I thought I would do it via a macro.


    Shg I shall give your code a try

    Regards
    John

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Carter, this is my second request for you to add code tags to the code in your original post. Please read the forum rules if you don't know how.

  6. #6
    Registered User
    Join Date
    10-30-2007
    Posts
    5
    Hi All
    I have tried Shg's code and I've hit a problem in that the code stops at
    Set wksFr = Workbooks("thisWorkbook").Sheets("thisSheet")
    This workbook is a variable name although the worksheet will always be the same name. If I hard code it to be the name of the workbook that I am working on then it works until I want to switch back to the source data workbook where it doesn't switch back (probably because my code is incorrect)

    I've added my code below so perhaps you can see where I'm going wrong.

    Please Login or Register  to view this content.
    Thanks for all your help and apologies if I have broken any protocols.

    Regards
    John
    Last edited by Leith Ross; 11-29-2007 at 06:22 PM.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello John,

    I wrapped your code for this time since you are still new here at the Forum. Here is how to do it in the future...

    How to Wrap your Code
    On the Message window Toolbar you will see the # icon. Select the text you want to appear in a Code Window, and Click the # icon. This will wrap the code for you. You can also do this manually by placing the tag [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.

    You can read the Forum rules by clicking on the link below.
    Forum Rules
    If you have any questions, contact me by clicking on my name Leith Ross

    Sincerely,
    Leith Ross

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I can't tell exactly what you're trying to do, but there's no need to unprotect the From worksheet in order to copy data from it, so there's no need to re-protect it.
    Please Login or Register  to view this content.
    Last edited by shg; 11-30-2007 at 12:05 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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