PDA

View Full Version : Solved: Copy and rename sheet in different name



halimi1306
04-20-2011, 03:25 AM
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/showthread.php?t=37080&highlight=copy+sheet+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
04-20-2011, 03:36 AM
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
04-20-2011, 04:03 AM
Try this instead



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

halimi1306
04-20-2011, 04:05 AM
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.
5914

halimi1306
04-20-2011, 04:09 AM
Try this instead

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

Thank xld

GTO
04-20-2011, 04:19 AM
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
04-20-2011, 04:22 AM
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
04-20-2011, 04:41 AM
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
04-20-2011, 04:52 AM
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
04-20-2011, 04:58 AM
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
04-20-2011, 05:02 AM
I could't find VBA Help, would you mind to give a link

GTO
04-20-2011, 05:36 AM
It worked fine for me using Excel 20120.

:eek: Wow! How many rows in that version?

halimi1306
04-20-2011, 07:58 AM
hehehe... I think typo error. Maybe 2010

Bob Phillips
04-20-2011, 09:58 AM
:eek: Wow! How many rows in that version?

Its a dogfood release!