Check out some similar questions!

kvinay_00 · Apr 27, 2012, 05:24 AM

Hello,

I have a working workbook with many linked sheets and formulas.

I want to export one sheet out of this (named "1") and save in new workbook without formula (i.e. save as value) and the name of the new workbook should be picked from a cell value (AD1).

Can any one help please with suitable macro?

Thanks in advance.

JBeaucaire · Apr 27, 2012, 11:27 PM

Something like this... you'll most likely have to play with the SaveAs command to get it working in your environment:

Code:

Option Explicit

Sub ExportSheet()
Dim Savepath As String

Savepath = "C:\Path\To\Save\Files\"    'remember the final \ in this string

With Sheets("1")
    .UsedRange.Copy
    Sheets.Add
    Range("A1").PasteSpecial xlPasteAll, xlPasteValues
    Sheets.Move
    ActiveWorkbook.SaveAs Range("AD1").Value & ".xlsx", xlNormal
    ActiveWorkbook.Close
End With

End Sub

kvinay_00 · Apr 29, 2012, 05:02 AM

Thanks JBeaucaire.

I have attached the sample file which already has some codes, can you look into and help me?

Thanks

kvinay_00 · May 1, 2012, 05:56 AM

The first file attached was locked for seeing vba codes, revised file attached, please refer that.

Sorry for the inconvenience.

Thanks

JBeaucaire · May 1, 2012, 11:59 AM

Then you only need to change one little thing:

Code:

ActiveWorkbook.SaveAs Range("AD1").Value & ".xls", xlNormal

Also, you can shorten up your CleanAll macro to:

Code:

Sub CleanAll()
   Range("K1:N1,R1:U1,X1:AB1,J2:M4,P2:Q4,U2:W2,AA2:AB2,U3:V6,C5:G6,J5:Q6,W4:AB6,A8:AB10,A12:AB16,B21:F40,Q21:S40,B43:k52,B56:K61,B65:K68,A71:AB71,A73:AB73").ClearContents
End Sub