Copy Sheet1, Sheet2, Sheet3 to new Workbook

[helpexcel]

Hi,

I want to copy 3 specific worksheets (Sheet1, Sheet2, Sheet3) to a new workbook. Only want to copy values and formatting, no formulas or references.

I want to call the new workbook "Eddie" and the current date.

thanks!

 

[Logit]

.
Try this :

Option Explicit


Sub Seperate_Sheets()


Dim Path1 As String


Path1 = ActiveWorkbook.Path & "\" & "Eddie" & Format(Now, " dd-mm-yyyy ")


Sheets(Array("sheet1", "sheet2", "sheet3")).Copy
    ActiveWorkbook.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close


End Sub

 

[mumps]

Another option:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    For Each ws In Sheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    ActiveWorkbook.SaveAs Filename:="C:\Test\Eddie" & Replace(Date, "/", "-") & ".xlsx" 'change path to suit your needs
    Application.ScreenUpdating = True
End Sub

 

[helpexcel]

@ Logit your code errors out on the path1 line at Format
@mumps works great. How do give the option of where to save? Also is there a way to delete a command button when copying?

 

[Logit]

.
Interesting .. it works without error here.

Try replacing the existing line of code with this one :

Path1 = ActiveWorkbook.Path & "\" & "Eddie " & Format(Now, "dd-mm-yyyy")

The only difference is the "dd-mm-yyy" portion. Previously a space existed before dd and after yyyy


​

 

[Logit]

.
You might also try :

Format(Now(), "dd-mm-yyyy")

 

[mumps]

To save to a different folder, just change the folder path where indicated in the code. Is there a command button on all 3 sheets or just one sheet? If one sheet, what is the sheet name?

 

[helpexcel]

I wanted give give the option of where to save. The command button is only on sheet1. Thanks!!

 

[mumps]

Assuming that the button name is "CommandButton1", try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, sPath As String
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    For Each ws In Sheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Sheets("Sheet1").Shapes("CommandButton1").Delete
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & "Eddie" & Replace(Date, "/", "-") & ".xlsx"
            Cancel = True
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[helpexcel]

getting an overflow error at ws.UsedRange.Value = ws.UsedRange.Value

Is this because there are more worksheets that aren't being copied?