Copy file and rename sheet tab

[tech2]

Hi,

Below is the code that I use to copy an excel file to another folder, which works fine.
My question is: how can I rename the sheet 'Report_6' to 'Sheet1' after it has been copied?
This has to be done when the workbook also named 'Report_6' is closed.

Thx
Tech2

Sub CopyFile()
'
' CopyFile Macro
'
'
FileCopy "C:\Documents and Settings\user\My Documents\Folder\Report_6.xls", "C:\Documents and Settings\user\Desktop\Folder\Report_6.xls"
End Sub

 

[taigovinda]

You have to open the file in order to rename the sheet.

Try:

Sub test1()
'
' CopyFile Macro
'
dim wkBk as workbook
set wkBk = workbooks.open("C:\Documents and Settings\user\My Documents\Folder\Report_6.xls")
 
wkbk.sheets("Report_6").name="Sheet1"
 
wkbk.saveas "C:\Documents and Settings\user\Desktop\Folder\Report_6.xls"
 
wkbk.close true
 
End Sub

Code untested and doesn't work in Excel 2007+... the SaveAs requires additional info in those versions. If using there, try recording the SaveAs with the macro recorder.

 

[tech2]

Thanks taigovinda,

Your code works Great!

The only thing is: there is a warning message that comes up confirming if I want to replace the file. Is there a line of code that would keep this message from displaying?

 

[taigovinda]

Yes, try this before and after the saveas line:

application.displayalerts = false
''''save as lne of code
application.displayalerts = true

actually... displayalerts I suppose is less specific and though I've not used it (and can't test because my Excel is tied up processing something), you may be better off to use application.alertbeforeoverwriting in this type of situation.