Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

export sheets into new file without formuals

0

hello

I  have  this  code  export specific  sheets  to  new  file  , but  I  don't  want   including   any formulas   .  the  formulas  are   existed  in  column E, G,I  . how  can  I  get  rid  of  the  formula  and  shows  as  a value,please?

Sub copysheet()
   Dim Fname As String

    Application.DisplayAlerts = False

       Sheets(Array("main", "report")).Copy
       wb_name = "sheets names"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\ WORLD\Desktop\" & wb_name & " lists " & Format(Date, "dd-mm-yy") & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

thanks

excel
MS Office Version: 2010
posted by: speed (rep: 40) on Tue Feb 22, 2022 at 11:32 am
Answer
Discuss

Answers

0
Selected Answer

Speed

I suggest you add the code in bold below (simple but commented to help you)

Sub copysheet()
   Dim Fname As String, ws As Worksheet

    Application.DisplayAlerts = False

        Sheets(Array("main", "report")).Copy
        'loop through new sheets
        For Each ws In ActiveWorkbook.Sheets
            ' remove all formulae
            ws.UsedRange = ws.UsedRange.Value
        Next ws
        wb_name = "sheets names"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\ WORLD\Desktop\" & wb_name & " lists " & Format(Date, "dd-mm-yy") & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Just checking- does your WORLD folder really begin with a space, like " WORLD" above?

Hope this works for you.

posted by: John_Ru (rep: 6142) on Wed Feb 23, 2022 at 1:56 pm
Discuss

Discussion

Hi John,
Just checking- does your WORLD folder really begin with a space, like " WORLD" above?
absolutely  no , sorry  about  it .
this  works very well . thanks  for  your  help.
speed (rep: 40) Feb 23, '22 at 2:33 pm
Good, glad that helped. Thanks for selecting my answer Speed. 
John_Ru (rep: 6142) Feb 23, '22 at 3:41 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login