Create new workbook with save as sheet

[fari1]

i'm trying to do is create a workbook in csv and in its sheet1 i want to copy contents of my sheet "PROF" of original workbook and then save it with the name of CIK No. plus cell value of B5 and current date and time. below code is very raw form of what i'm trying to achieve, would appreciate any quick help on this. Also if there's any way that rather than copying of contents of my original workbook, the sheet is just saved as in new workbook. this would avoid memory leak and also clipboard will not be burdened.

Sub AddNewWBook()
        Dim XL As Excel.Application
        Dim str1 As Long
        Set XL = New Excel.Application
        XL.Workbooks.Add
        XL.Visible = True
        
        XL.ActiveWorkbook.Activate
        XL.Sheets(1).Select
        
        MkDir ThisWorkbook.Path & "\" & "CBG No."
        str1 = ""
        str1 = ThisWorkbook.Path & "\CBG No.\CBG No.&sheets("prof").Range("B5")"_" & DateTime.Day(Now) & "_" & DateTime.Month(Now) & "_" & DateTime.Year(Now) & "--" & DateTime.Hour(Now) & "_" & DateTime.Minute(Now) & "_" & DateTime.Second(Now) & ".xls"
        XL.ActiveWorkbook.SaveAs
        If Dir(str1) <> "" Then
            Cells(i, 6) = str1
        End If
        XL.ActiveWorkbook.Close True
        XL.Quit
        Set XL = Nothing
Next
End Sub

 

[justme1122]

from google search I got this that mite be help full for you:

http://www.mrexcel.com/archive/VBA/26746.html

 

[fari1]

hi thanks for the response, these codes are to open activeworkbook, while my workbook is already opened and also it doesn't serve my other purposes, will be glad if u assisst on it completely