Export worksheet in New File by VBA code

[shahzad4u_ksa]

Hi,

I arranged one worksheet (Data), there are lot of formulas and it is
linked to other sheets. I need button to prepare a copy of (Data)
sheet to new file, without formula I mean special past - only Values.
Actually my (Data) sheet is Fit to one page as 100% view.

I need code to copy the Data sheet to new file asking location to save
this file with full formatting and actual margens.

Pls help me.


Shahzad
Madinah

 

[Bernie Deitrick]

Sub CopyDataSheet()
Dim mySht As Worksheet

Sheets("Data").Copy Before:=Sheets(1)
With ActiveSheet
..Name = "Data Copy"
With .Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
..Move
End With
ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("New data file.xls", , , "Enter a new file name")
End Sub

HTH,
Bernie
MS Excel MVP

 

[shahzad4u_ksa]

Sub CopyDataSheet()
Dim mySht As Worksheet

Sheets("Data").Copy Before:=Sheets(1)
With ActiveSheet
.Name = "Data Copy"
With .Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
.Move
End With
ActiveWorkbook.SaveAs Application.GetSaveAsFilename _
("New data file.xls", , , "Enter a new file name")
End Sub

HTH,
Bernie
MS Excel MVP












- Show quoted text -

Dear Bernie,

Thank you very much for sending me the above solution, I test it and
it is working great. there is only one small problem I seen. Actually
I have some Buttons on the Data sheet, when I run your code, all the
buttons are also copied in to new file. and I dont need any button in
new file.

I made all the buttons from "FORM tool menu". now what to do, copy
Data sheet in new file without buttons.

Thanks in advance.

Regards.

Shahzad

 

[Bernie Deitrick]

Sub CopyDataSheet2()
Dim mySht As Worksheet

Sheets("Data").Copy Before:=Sheets(1)
With ActiveSheet
.Shapes.SelectAll
Selection.Delete

.... Rest of code

HTH,
Bernie
MS Excel MVP