+ Reply to Thread
Results 1 to 6 of 6

Moving between original and saved copy of a workbook

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Telford, England
    MS-Off Ver
    Excel 2016
    Posts
    22

    Moving between original and saved copy of a workbook

    Hi all,
    Not sure if this has been covered before and if so I apologise in advance (and can somebody please point me to the relevant thread). I’m new to VB so I’m learning on the run; but have managed to work out how to do most things so far; except this one…
    I have a number of workbooks that I copy either single or multiple sheets from to save and send; all using a date variable as part of the new workbook title.
    What I’m looking to do is to be able to save the new workbook and then move between it and the original master copy within the same Macro as I do the copy and save in. I’ve had a bit of a trawl of the web but can’t seem to find a solution to be able to activate original document (which I can do) then reactivate the “new” workbook.
    Because I’m sending the “new” workbook directly through Outlook I need to finish in that one for that part of the Macro to work (as far as I’m aware). What I want to do is to incorporate a separate Macro that currently needs running separately in the master workbook; all as part of the same single click.

    Any help would be appreciated; sorry if I’ve been a little vague or confusing with the explanation

    Thanks,
    Craig...

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Moving between original and saved copy of a workbook

    Craig

    One thing you can do is create reference to the relevant workbooks at the appropriate points in the code.

    Those references can then be used in the rest of the code whenever you need to refer to the workbooks.

    By the way, it's very rarely that you need to activate/select when working between worksheets/workbooks.

    PS Not sure about having to 'finish' in a workbook to be able to send it via Outlook, eg if you are sending it as an attachment all you need is the file path and name.s
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-04-2013
    Location
    Telford, England
    MS-Off Ver
    Excel 2016
    Posts
    22

    Re: Moving between original and saved copy of a workbook

    Hi Norie,

    Thanks for your quick reply
    I use range references for both the file name and path because both vary depending on the month (we save in to month folders to make finding things easier later on)
    For example:
    myFilename = Range("AH1").Value
    myDir = Range("AH2").Value
    When I try to use the filename range for the active reference though it always errors.
    Not sure if that’s what you mean though…?
    Craig...

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Moving between original and saved copy of a workbook

    Not quite.

    Can you post the code you are using to create/save the new workbook(s)?

  5. #5
    Registered User
    Join Date
    06-04-2013
    Location
    Telford, England
    MS-Off Ver
    Excel 2016
    Posts
    22

    Re: Moving between original and saved copy of a workbook

    Hi Norie,

    Apologies for not replying sooner, end of the work day! Thought I might have misunderstood you; as I said before, I'm new to all this; only been playing with VB for a couple of months

    code below:

    Please Login or Register  to view this content.
    I'm looking to be able to go back to the original document after saving the new workbook then return to the new workbook to email it.

    Thanks for the help

  6. #6
    Registered User
    Join Date
    06-04-2013
    Location
    Telford, England
    MS-Off Ver
    Excel 2016
    Posts
    22

    Re: Moving between original and saved copy of a workbook

    Hi Norie,

    Thanks to a suggestion from a colleague I’ve managed to solve this with a workaround; I’ve added code in to save the new workbook as “Temp” in a specified folder. This allows me to move between workbooks and do what’s required, then save the “Temp” workbook as the correct name and delete the “Temp” document from the save folder.
    If you have a more elegant way of doing this I’d still love to hear it (I’m fairly sure none of my VB coding is by any means elegant!)
    Thanks for the help either way.
    Regards,
    Craig...

+ 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. Saved Copies of Workbook Reference Original
    By mattdh12 in forum Excel General
    Replies: 1
    Last Post: 04-10-2014, 03:25 AM
  2. Copy graph to new workbook while leaving data in original workbook
    By smjpl in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-10-2012, 12:49 PM
  3. Replies: 7
    Last Post: 09-28-2008, 10:00 AM
  4. Replies: 1
    Last Post: 04-01-2006, 03:50 PM
  5. How can I see a copy of a saved workbook before I saved it again?
    By Norma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2005, 06:06 AM

Tags for this Thread

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