PDA

View Full Version : Copying Worksheet to another book with new name



Saladsamurai
10-29-2009, 05:45 AM
I am currently using a code that looks like

Sub CopySheet()

Dim InstallDir As String


InstallDir = "Z:\temp\CaseyTemp\Test\"

Workbooks.Open (InstallDir & "TestCopySheet.xls")

With Workbooks("CopySheet.xls")
Sheets(1).Copy Before:=Workbooks("TestCopySheet.xls").Sheets(1)
End With



' Workbooks("TestCopySheet.xls").Save
' Workbooks("TestCopySheet.xls").Close

End Sub


Is there a way to add in a new name to the copy being sent to "TestCopySheet.xls" ?

markmrw
10-29-2009, 05:48 AM
ActiveSheet.Name = "New Sheet Name"

Saladsamurai
10-29-2009, 06:01 AM
ActiveSheet.Name = "New Sheet Name"

Hi there :)

This requires that the new sheet be active right? I am trying to automate this, so I am not sure if that will always be the case.

I am trying this. It is "Copying" the sheet over to the other workbook BUT it is not actually copying any of the data in the worksheet...

Sub CopySheet()

Dim InstallDir As String
Dim Name As String

InstallDir = "Z:\temp\CaseyTemp\Test\"

Workbooks.Open (InstallDir & "TestCopySheet.xls")

With Workbooks("CopySheet.xls")
Sheets(1).Copy Before:=Workbooks("TestCopySheet.xls").Sheets(1)
Workbooks("TestCopySheet.xls").Sheets(1).Name = _
Workbooks("CopySheet.xls").Worksheets(1).Name
End With



' Workbooks("TestCopySheet.xls").Save
' Workbooks("TestCopySheet.xls").Close

End Sub

I can tell that it is acually copying the sheet because there is only one sheet in "TestCopySheet" and after the Sub is run, there are two. But the contents of the sheet being copied is not coming over?


EDIT:

I think I got it. The 'With' was superflous and apparently confusing Excel.