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...

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...

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:

Sub EmailCopyCol()
'
' EmailCopyCol Macro
'

'
    Dim Path As String
Path = Range("AH2").Value
 
If Len(Dir(Path, vbDirectory)) = 0 Then
    MkDir (Path)
End If
    
    Application.ScreenUpdating = False
  
    Sheets("Reqested Collections").Select
    Sheets("Reqested Collections").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     ActiveSheet.Range("$B$3:$AM$5000").AutoFilter field:=2, Criteria1:= _
        "=Complete", Operator:=xlOr, Criteria2:="="
    Rows("4:5000").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$B$3:$AM$48").AutoFilter field:=2
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Select
    Application.CutCopyMode = False
    Selection.Cut
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 3")).Select
    Application.CutCopyMode = False
    Selection.Cut
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 4")).Select
    Application.CutCopyMode = False
    Selection.Cut
    myFilename = Range("AH1").Value
    myDir = Range("AH2").Value
    ActiveWorkbook.SaveAs Filename:= _
    myDir & "\" & myFilename, _
    FileFormat:=51, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    
    Application.DisplayAlerts = False
    Range("O1:Q1").Select
    Selection.ClearContents
    Range("AH1:AH2").Select
    Selection.ClearContents
    Application.ScreenUpdating = True
    Range("D4").Select
    ActiveCell.End(xlDown).Offset(1, 0).Select
    ActiveWorkbook.Save
    
    'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)

For Each ToRecipient In Array("Collection/Delivery Distribution List")
OlMail.Recipients.Add ToRecipient
Next ToRecipient

For Each CcRecipient In Array()
With OlMail.Recipients.Add(CcRecipient)
.Type = olCC
End With
Next CcRecipient

'fill in Subject field
OlMail.Subject = myFilename

'Add the active workbook as an attachment
OlMail.Attachments.Add ActiveWorkbook.FullName

'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

End Sub

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

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...