1

I have created code to copy and paste a range of cells from one workbook and paste into a new workbook and then save.

What I need to do is add more code to save as a new version, if saving a new batch of records on the same date. Any help would be great thanks

Sub Export()

Dim sPath As String

sPath = Environ("userprofile") & "\Training\"


Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim filename As String


'~~> Source/Input Workbook
Set wbI = ThisWorkbook
'~~> Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Reporting")

'~~> 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
    filename = wsI.Range("C2").Text
   .SaveAs filename:=sPath & Format(Date, "yyyy mm dd ") & filename, FileFormat:=56



    '~~> Copy the range
    wsI.Range("B6:AA26").Copy

    '~~> Paste it in say Cell A1. Change as applicable
    wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    wsO.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    .Save

End With

End Sub

Thanks in advance

EstelleW
  • 13
  • 1
  • 7
  • 1
    [This](https://stackoverflow.com/questions/16351249/vba-check-if-file-exists) could be of use. Once you know if the file exists, you can change the name, and check again until you get to an unused name, at which point you can save – cybernetic.nomad Oct 22 '18 at 15:39
  • @cybernetic.nomad thanks, I need to create the file in the first instance, so it will always exist, what I'm trying to do is if in the same day there are more records to copy and paste, that i can use the same macro to save the same date but with a new version as the name – EstelleW Oct 22 '18 at 15:52
  • 1
    if you dont have any specific version nomenclature in mind, then simply add timestamp i.e. hhmmss to your file name. If you want to do something like File1_V1.xlsm , File1_V2 etc then that's a little bit more code. – cyboashu Oct 22 '18 at 15:55
  • From your question: "add more code to see if the saved file already exists", From your comment: "it will always exist". So does is exists or not? Can you please [edit your question](https://stackoverflow.com/posts/52932906/edit) to clarify? – cybernetic.nomad Oct 22 '18 at 15:55
  • @cybernetic.nomad - thanks have edited now, sorry for the confusion – EstelleW Oct 22 '18 at 16:05
  • 1
    @cyboashu great simple solution - why try to make something more complicated than it should be! that will work a treat thanks – EstelleW Oct 22 '18 at 16:07

0 Answers0