Copying worksheet to new file by macro

[Ruatha]

Hi.
By pressing a commandbutton, I want to copy a worksheet (The content,
not the functions) into a new file.
There are 52 worksheets called "Vecka 1" to "Vecka 52".
The user enters a number in a cell and presses a command button next to
that cell, and then the worksheet with the number the user has given
should be copied to a file called "Vecka xx.xls" where xls is the
number of the worksheet." (Vecka 0-52).

How to do that?

 

[Ron de Bruin]

Hi Ruatha

Sub test()
Dim wb As Workbook
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb.Sheets(1)
.UsedRange.Copy
.UsedRange.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
End Sub


This example will create a workbook for every sheet
http://www.rondebruin.nl/copy6.htm

 

[Ruatha]

Thanx.
It works good and makes a file for the active worksheet, now I'll try
to adapt it to my needs!

Thanx again Ron de Bruin