Export worksheet in New File by VBA code
 

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

Export worksheet in New File by VBA code
 

Sub CopyDataSheet()
Dim mySht As Worksheet

Sheets("Data").Copy Befo=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


wrote in message
ups.com...

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

Export worksheet in New File by VBA code
 

On Jul 3, 3:06 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Sub CopyDataSheet()
Dim mySht As Worksheet

Sheets("Data").Copy Befo=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

wrote in message

ups.com...





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- Hide quoted text -

- 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

Export worksheet in New File by VBA code
 

Sub CopyDataSheet2()
Dim mySht As Worksheet

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

.... Rest of code

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
On Jul 3, 3:06 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Sub CopyDataSheet()
Dim mySht As Worksheet

Sheets("Data").Copy Befo=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

wrote in message

ups.com...





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- Hide quoted text -

- 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