Need a Macro To export Specific sheets of excel to new file as VALUES ONLY

[Hemanth Golla]

Currently i am using following VBA Code to export sheets. But i need the export the specific sheets as values only ( Remove formula's and links ) and Remove Sheet 1 in new file.

Option Explicit

Sub Export_Worksheets()

Dim wbSource As Workbook, wbTarget As Workbook
Dim worksheetList As String 'Use colon as seperator since you cannot have colon in your worksheet name'
Dim worksheetArr As Variant
Dim arrIndx As Long

On Error GoTo errHandle
worksheetList = "Jan:Feb:Mar:Aug"

worksheetArr = Split(worksheetList, ":")

If UBound(worksheetArr) = -1 Then Exit Sub

Set wbSource = ThisWorkbook

Set wbTarget = Workbooks.Add

For arrIndx = LBound(worksheetArr) To UBound(worksheetArr)
ThisWorkbook.Worksheets(worksheetArr(arrIndx)).Copy wbTarget.Worksheets(wbTarget.Worksheets.Count)
Next arrIndx

MsgBox "Export complete.", vbInformation

CleanObjects:
Set wbTarget = Nothing
Set wbSource = Nothing

Exit Sub

errHandle:
MsgBox "Error: " & Err.Description, vbExclamation
GoTo CleanObjects
End Sub

 

[HaHoBe]

Hi Hemanth Golla,

how about

Sub Export_Worksheets()
' https://www.mrexcel.com/board/threads/need-a-macro-to-export-specific-sheets-of-excel-to-new-file-as-values-only.1223360/

Dim wbTarget As Workbook
Dim worksheetList As String 'Use colon as seperator since you cannot have colon in your worksheet name'
Dim worksheetArr As Variant
Dim arrIndx As Long
Dim ws As Worksheet

On Error GoTo errHandle
worksheetList = "Jan:Feb:Mar:Aug"

worksheetArr = Split(worksheetList, ":")

If UBound(worksheetArr) = -1 Then Exit Sub

Worksheets(worksheetArr).Copy
Set wbTarget = ActiveWorkbook

For Each ws In wbTarget.Worksheets
  With ws.UsedRange
    .Value = .Value
  End With
Next ws

MsgBox "Export complete.", vbInformation

CleanObjects:
Set wbTarget = Nothing

Exit Sub

errHandle:
MsgBox "Error: " & Err.Description, vbExclamation
GoTo CleanObjects
End Sub

Ciao,
Holger

 

[Hemanth Golla]

Hi bro,

Thanks for your help. it worked well. Thank you so much.