• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Participant

      Copy Range Data to a new Excel File

      Hi,

      I have used the forum and found ‘Copy Data from one Worksheet to Another in Excel VBA’ which worked fantastic, using the below code:

      Sub SAMPLE()
      Sheets(“Sheet1”).Select
      Range(“E1:G513”).Select
      Selection.Copy
      Sheets(“Sheet2”).Select
      Range(“A2”).Select
      ActiveSheet.Paste
      End Sub

      I liked this code as it kept the comments I had inserted on the tabs and copied them to ‘Sheet 2’

      What I would like to do, is copy Sheet 1 in exactly the same way, and when running the macro, Save as a new file (acting as a copy).

       

      Can anyone help me please? I only want to save a range of the sheet 1 data, which is why I am not using a macro to save a copy of the sheet.

      I did have this code:

      Option Explicit

      Sub Sample()
      Dim wbI As Workbook, wbO As Workbook
      Dim wsI As Worksheet, wsO As Worksheet

      ‘~~> Source/Input Workbook
      Set wbI = ThisWorkbook
      ‘~~> Set the relevant sheet from where you want to copy
      Set wsI = wbI.Sheets(“Sheet1”)

      ‘~~> Destination/Output Workbook
      Set wbO = Workbooks.Add

      With wbO
      ‘~~> Set the relevant sheet to where you want to paste
      Set wsO = wbO.Sheets(“Sheet1″)

      ‘~~>. Save the file
      .SaveAs Filename:=”C:\Desktop\Customer Copy.xlsm”, FileFormat:=56

      ‘~~> Copy the range
      wsI.Range(“D1:G515”).Copy

      ‘~~> Paste it in say Cell A2. Change as applicable
      wsO.Range(“A2”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
      End With
      End Sub

      Which saves as a new file which is great, but it looses the inserted comments I had.

      Thanks

      Asked by PeteMcKinnon on December 7, 2017 in VBA: Macros.
      1 Answers
      Expert

      Not sure if this will help. When you save your file click Tools>General Options and then click on Always create backup.  No macro needed to keep a backup of your work.

      Save a backup fileSave a backup file

      Answered by Hyside2 on December 30, 2017..
      • Hi Hyside2 Thank you for the comment, this wont work as I only want to copy selected information, which is why I was using the macro,

        The reason for this, is that I have a Master file, and then certain information on a Copy which then means when a colleague or myself update the Master it updates the copy also.

        on January 2, 2018.
      Cancel
      Add comment