Solved: Copy and rename sheet in different name

[halimi1306]

Hi there,

I want to copy my "Master" sheet to new wb and rename it as "Simulation1", "Simulation2" and so on. But here I only need to copy table format, value format and without formula.

Below is the code from Simon referring to thread http://www.vbaexpress.com/forum/show...t+to+new+sheet

But this code give me error '1004'. Hope some one could help me on this.

Sub CopySheetAll()
    Dim Master As String
    MyBook = ActiveWorkbook.Name
    ActiveSheet.Copy
    ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs ("Simulation" & ".xls")
    Workbooks(Master).Activate
End Sub 

[GTO]

From your present code, you should error at:

    ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
SkipBlanks:= _
        False, Transpose:=False

...as there's nothing on the clipboard to paste.

[Bob Phillips]

Try this instead

[vba]

Sub CopySheetAll()
Dim Master As String
Dim MyBook As String
MyBook = ActiveWorkbook.Name
ActiveSheet.UsedRange.Copy
ActiveSheet.UsedRange.Cells(1, 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs ("Simulation" & ".xls")
Workbooks(Master).Activate
End Sub
[/vba]

[halimi1306]

hi,
Attached herewith is the sample file. I want the result from sheet "Model" to be copied in new workbook and need to be rename as "Simulation". I need to run this for few time and rename it i.e. "Simulation1" , "Simulation2" and so on. Pls have a look at my file for better understanding.
CPI Model v.6.0.xls

[halimi1306]

Try this instead

I got error 1004, and how to copy table format? Pls have a look at my attachment.

Thank xld

[GTO]

I am reading your request a bit differently than Bob is. I took it that you want to copy the sheet to a new wb, and just retain the values therein (the new wb). I got this far, but your sheet is protected and w/a password...

Could you reload the attachement or tell us the sheet's password?

Sub exa()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
    
    Path = ThisWorkbook.Path & "\"
    
    Set wks = ThisWorkbook.Worksheets("Model")
    Set wb = Workbooks.Add(xlWorksheet)
    wks.Copy After:=wb.Worksheets(1)
    Application.DisplayAlerts = False
    wb.Worksheets(1).Delete
    Application.DisplayAlerts = True
    
    Set wks = wb.Worksheets(1)
    
    'unprotect or protect w/UserInterfaceOnly here
    
    wks.UsedRange.Value = wks.UsedRange.Value
    
    wb.SaveAs Path & "Simulation.xls"
End Sub

[halimi1306]

I am reading your request a bit differently than Bob is. I took it that you want to copy the sheet to a new wb, and just retain the values therein (the new wb). I got this far, but your sheet is protected and w/a password...

Could you reload the attachement or tell us the sheet's password?
[code]Sub exa()

Ops! Sorry.

pasword: asd

I try to run your code but it return error.
one more, if you see my sheet, there's a tab name Dashboard. Is it possible to copy model tab and dashboard in same new wb because those two is the result from each simulation. THe Dashboard tab is link to model tab.

[GTO]

In a junk copy of your wb, try the code with wks.Protect "asd", , , , True added below the comment. I cannot really test, as IFERROR is not available in 2003. I can open your attached wb and the values show fine, but if I copy the sheet, everything goes #N/A

[halimi1306]

In a junk copy of your wb, try the code with wks.Protect "asd", , , , True added below the comment. I cannot really test, as IFERROR is not available in 2003. I can open your attached wb and the values show fine, but if I copy the sheet, everything goes #N/A

It works great! But if I run for second round, how to rename the sheet as "Simulation 2" and so on for 3rd run...

[Bob Phillips]

It worked fine for me using Excel 20120.

Checkout SaveCopyAs in VBA Help, this will copy the workbook, then delete all sheets except your nominated sheets.

[halimi1306]

I could't find VBA Help, would you mind to give a link

[GTO]

It worked fine for me using Excel 20120.

Wow! How many rows in that version?

[halimi1306]

hehehe... I think typo error. Maybe 2010

[Bob Phillips]

Wow! How many rows in that version?

Its a dogfood release!