Save Workbook as Copy and set it as variable-CodePudding

Let's say I have a function that takes a value of a cell from one of the sheets and makes a copy of that file under that name in the specified directory:

Function SavePeerGroupAsFile(source_file, peer_group, file_path)


SavePeerGroupAsFile = source_file.SaveCopyAs(filename:=file_path & peer_group & ".xlsm")



End Function

I would like to be able to use it in my Main so that after the function is called, that workbook is set as a variable, so I could directly work on it and do some other stuff. To call it, in my Main, I'm using:

Set peer_wrk = SavePeerGroupAsFile(src_wrk, peer_group_name, peer_group_dir)

I can see the file saved under correct name in the right directory but right after function is called it throws an error:

Any idea how this should be done correctly?

CodePudding user response：

You need to open the copied workbook:

Function SavePeerGroupAsFile( _
   ByVal source_file As Workbook, _
   ByVal peer_group As String, _
   ByVal file_path As String _
) As Workbook
   
   source_file.SaveCopyAs Filename:=file_path & peer_group & ".xlsm"
   Set SavePeerGroupAsFile = Workbooks.Open(file_path & peer_group & ".xlsm")
End Function