Copy and saving tab on file location

[Shakti Bansal]

Hi,

Can anyone help me with VBA that create a copy of particular tab (For example JE) and save it on file location (with paste special values in case of formulas). The name of the file should be value that is in cell C3.

 

[Kenneth Hobson]

I guess you wanted xlsm file type?

Sub CopySheetToNewWorkbook()
  Dim wb As Workbook
  Set wb = Workbooks.Add(xlWBATWorksheet)
  ThisWorkbook.Worksheets("JE").Copy After:=wb.Worksheets(1)
  Application.DisplayAlerts = False
  wb.Worksheets(1).Delete
  wb.SaveAs ThisWorkbook.Path & "\" & _
    ThisWorkbook.Worksheets("JE").Range("C3").Value & ".xlsm", _
    xlOpenXMLWorkbookMacroEnabled 'xlOpenXMLWorkbook
  wb.Close False
  Application.DisplayAlerts = True
End Sub

 

[Shakti Bansal]

Thanks a Kenneth. That really helps..

 

[Shakti Bansal]

I guess you wanted xlsm file type?

I wanted xlsx version. I have changed the same in the code from my end.

Once again,many thanks