copy worksheet and values

[wilg]

Hi, have this code below that copies a worksheet from a workbook and pastes into a new workbook and values only so no need for referencing after. Only problem is it creates 2 new workbooks, I only want one.
Can you see what's causing it to create 2 new workbooks with values only?

Sub PasteShtVal1()
Call UnProtectAll
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Copy
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Copy
Range("A1").Select
Call ProtectAll
End Sub

[Simon Lloyd]

How about this:
Sub PasteShtVal1()
Dim MyBook As String
Call UnProtectAll
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Copy
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
MyBook = ActiveWorkbook.Name

Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Copy Before:=Workbooks(MyBook).Sheets(1)
Range("A1").Select
Call ProtectAll
End Sub

[wilg]

Wow, quick response thanks. This keeps the workbook down to creating only one, but it creates 2 sheets in that workbook.
I would like only one workbook, one sheet that was values only..
Thanks in advance again..

[Simon Lloyd]

But you are using the macro to copy selected sheets twice, so that will add more sheets, are you trying to copy the data of another sheet to the newly made sheet?

can you supply a dummy workbook, the data and structure should be of the same format but can be dummy data.

[wilg]

I'm really only trying to copy one worksheet from my master workbook into a new workbook, but only want to kill the formulas when it goes into the new workbook so no lost references.

[wilg]

Actually, if I modified to this....

Sub PasteShtVal11()
Dim MyBook As String
Call UnProtectAll
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Copy
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
MyBook = ActiveWorkbook.Name

'Application.CutCopyMode = False
'ActiveWindow.SelectedSheets.Copy Before:=Workbooks(MyBook).Sheets(1)
Range("A1").Select
Call ProtectAll
End Sub


It seem to have done the job.

[wilg]

Now is there a way to have the new workbook's name = the sheets name?

[Simon Lloyd]

Just one sheet?????
try this:
Sub PasteShtVal1()
Dim MyBook As String
MyBook = ActiveWorkbook.Name
Call UnProtectAll
ActiveSheet.Copy
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
Application.CutCopyMode = False
ActiveWorkbook.Name = "Copied Values" & Format(Date, "dd-mm-yyyy") & ".xls"
Call ProtectAll
Workbooks(MyBook).Activate
End Sub

[wilg]

I get an error " can't assign to read-only property

ActiveWorkbook.Name = "Copied Values" & Format(Date, "dd-mm-yyyy") & ".xls"

[Simon Lloyd]

I get no problem xl2003, so your copying a read only workbook?

[wilg]

My Master workbook is not a read only, and I'm using xl2003 as well. I highlights .name and give me the error can't assign to read-only property

[Simon Lloyd]

the problem is the Call to protect, move that to after the naming of the workbook :)

[wilg]

I tried, but didn't work for me. I tried commenting out the protect too, but no luck

[wilg]

One thing to note I see is it give the error before it copies the worksheet and the new workbook is active..

[Simon Lloyd]

Ok try this, if it doesn't work i'll need site of your workbookSub PasteShtVal1()
Dim MyBook As String
MyBook = ActiveWorkbook.Name
Call UnProtectAll
ActiveSheet.Copy
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
Application.CutCopyMode = False
ActiveWorkbook.SaveAs ("Copied Values" & Format(Date, "dd-mm-yyyy") & ".xls")
Call ProtectAll
Workbooks(MyBook).Activate
End Sub

[wilg]

That's it, it works! Thanks Simon for your patience and excellent work.

[Simon Lloyd]

Wilg, if this is resolved please mark the thread as solved by going to thread tools>mark solved

[wilg]

Hi Simon, one last thing. I've adapted the code you provided to save as the sheet name which works well. However if the newly created workbook already exists with that name it prompts to replace. If I select yes everything is fine. But if I select no it throws an error.
Is there a way to automatically replace without prompting to overite?
Sub PasteShtVal1()
On Error GoTo ERRIX
Dim MyBook As String
MyBook = ActiveWorkbook.Name
Call UnProtectAll
ActiveSheet.Copy
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
Application.CutCopyMode = False
ActiveWorkbook.SaveAs ActiveSheet.Name & Format(Date, " mmm-dd-yyyy") '("Copied Values " & Format(Date, "dd-mm-yyyy") & ".xls")

'Workbooks(MyBook).Activate
Call ProtectAll
ERRIX:
Call ProtectAll
Exit Sub
End Sub

[Simon Lloyd]

If you really dont want a choice then just supress the alerts, like this:Sub PasteShtVal1()
On Error GoTo ERRIX
Dim MyBook As String
MyBook = ActiveWorkbook.Name
Call UnProtectAll
ActiveSheet.Copy
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ActiveSheet.Name & Format(Date, " mmm-dd-yyyy") '("Copied Values " & Format(Date, "dd-mm-yyyy") & ".xls")
Application.DisplayAlerts = True
'Workbooks(MyBook).Activate
Call ProtectAll
ERRIX:
Call ProtectAll
Exit Sub
End Sub