Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Making a Copy of the Workbook Before Transferring Data

    Hi Experts,

    The code below is for transferring data from WB-A to WK-B. It works well. But so far I have been able to make a backup copy of WB-B before I transfer data.

    Sub CopyOpenItems()
    Dim wbTarget As Workbook 'workbook where the data is to be pasted
    Dim wbThis As Workbook 'workbook from where the data is to copied
    Dim strName As String
    Dim MyRange As String

    MyRange = Application.InputBox("Enter a number")


    'set to the current active workbook (the source book)
    Set wbThis = ActiveWorkbook

    'Name of the target Workbook
    strName = "C:\Users\Jack\Desktop\Excel Stuff\target"

    'Open target Workbook using strName
    Set wbTarget = Workbooks.Open(strName)

    'Clear range of the Target Workbook
    wbTarget.Sheets(1).Range(MyRange).ClearContents

    'Activate the source workbook
    wbThis.Activate

    'clear any thing on clipboard to maximize available memory
    Application.CutCopyMode = False

    'copy the range from source book
    wbThis.Sheets(1).Range(MyRange).Copy wbTarget.Sheets(1).Range(MyRange)

    'clear any thing on clipboard to maximize available memory
    Application.CutCopyMode = False

    'save the target book
    wbTarget.Save

    'Close Target workbook
    wbTarget.Close

    'Activate the source workbook
    wbThis.Activate

    'Clear memory
    Set wbTarget = Nothing
    Set wbThis = Nothing

    End Sub

    Here is the code I am trying to adapt to the code above. I thought it would work after WK-B is opened and would save a backup copy in the same file. So far no luck.

    ThisWorkbook.SaveCopyAs _
    Filename:=ThisWorkbook.Path & "\" & _
    Format(Now(), "yyyy-mm-dd hh mm AMPM") & " " & _
    ThisWorkbook.Name


    Any help would be appreciated.
    Last edited by Excelnewbie; 2016-04-11 at 21:19.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Have you tried replacing in your code
    ThisWorkbook.SaveCopyAs
    with
    wbTarget.SaveCopyAs

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2016-04-12)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy,

    Thanks for your suggestion. I tired your suggestion and it didn't help in this particular situation. Big thanks to you for your suggestion.

    After hours of hunting I found the following code that solved the problem:

    Sub WorkbookSaveCopyAs3()
    'use the Workbook.SaveCopyAs Method to save a copy of a specified workbook:

    'open a specified workbook in the current directory:
    Workbooks.Open "C:\MyDocuments\Folder2\ExcelFile.xlsx"

    'the opened workbook becomes the active workbook:
    MsgBox ActiveWorkbook.Name

    'save a copy of the opened workbook, specifying a file name (the workbook and its copy being saved should be of same file format).
    ActiveWorkbook.SaveCopyAs "C:\MyDocuments\Folder2\Copy of ExcelFile.xlsx"

    'the opened workbook remains the active workbook, the saved copy remains closed:
    MsgBox ActiveWorkbook.Name

    'close the opened workbook - note that neither the opened workbook or the saved copy will be open after this command:

    ActiveWorkbook.Close

    End Sub
    Last edited by Excelnewbie; 2016-04-12 at 23:16.

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    I should have posted the full suggestion to make it clearer. See full adapted code below.
    The code you found that solved your problem assumes that 'the opened workbook becomes the active workbook". This is NOT always the case. Which is why I would prefer to use the adapted code shown below.
    Code:
     Sub CopyOpenItems()
     Dim wbTarget As Workbook 'workbook where the data is to be pasted
     Dim wbThis As Workbook 'workbook from where the data is to copied
     Dim strName As String
     Dim MyRange As String
    
     MyRange = Application.InputBox("Enter a number")
    
    
     'set to the current active workbook (the source book)
     Set wbThis = ActiveWorkbook
    
     'Name of the target Workbook
     'strName = "C:\Users\Jack\Desktop\Excel Stuff\target"
    
     'Open target Workbook using strName
     Set wbTarget = Workbooks.Open(strName)
    
     wbTarget.SaveCopyAs _
     Filename:=ThisWorkbook.Path & "\" & _
     Format(Now(), "yyyy-mm-dd hh mm AMPM") & " " & _
     wbTarget.Name
    
     'Clear range of the Target Workbook
     wbTarget.Sheets(1).Range(MyRange).ClearContents
    
     'Activate the source workbook
     wbThis.Activate
    
     'clear any thing on clipboard to maximize available memory
     Application.CutCopyMode = False
    
     'copy the range from source book
     wbThis.Sheets(1).Range(MyRange).Copy wbTarget.Sheets(1).Range(MyRange)
    
     'clear any thing on clipboard to maximize available memory
     Application.CutCopyMode = False
    
     'save the target book
     wbTarget.Save
    
     'Close Target workbook
     wbTarget.Close
    
     'Activate the source workbook
     wbThis.Activate
    
     'Clear memory
     Set wbTarget = Nothing
     Set wbThis = Nothing
    
     End Sub
    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    Excelnewbie (2016-04-13)

  7. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Zeddy,

    Thank you for taking the time to clean the code up. I appreciate it. You and the other Experts on this site have been great teachers/friends.

Posting Permissions

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