Copy file and next instruction

[tech2]

Hi,

Below is a macro that copies a file. I'm also trying to include some additional instructions after the file is copied but I get a 'Run-time error 9' 'subscript out of range' on the 'Sheets("Report").Select', line.

I also would like some help adding additional code on file copy that would copy the latest file by date and time. The file name is always the same but the extension is different and it could be created several times a day.

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"
Sheets("Report").Select
Sheets("Report").Name = "Sheet1"
ActiveWindow.SmallScroll ToRight:=2
Range("M1").Select
Selection.ClearContents
End Sub

 

[Gerald Higgins]

At the point when this line

'Sheets("Report").Select'

runs, do you actually have a sheet called "Report" ?

 

[tech2]

Yes, the workbook is named 'Report' and the sheet is named 'Report'. The code is renaming the sheet to 'Sheet1'.

 

[Gerald Higgins]

Well I tested this myself in a few different scenarios and the only way I could get it to return that error message on that line was if I didn't have a sheet called "Report".

Is it possible that the sheet is called "Re port" or "Report " or something similar ?
Is it possible that, if you are re-naming sheets, the sheet is not actually called Report at the point that that code line runs ?

 

[Zaf]

As mentioned, you need to be sure that this sheet actually exists in the active workbook.

You may need to add this line before your code...

Windows("Book 1").Activate 'Substitute "Book 1" with the workbook name  'that contains this 'Results' sheet...

You can also check the names of all sheets that exist in the active workbook by running the following code...

Public Sub Test()
 
'Print the names of all sheets in the active workbook.
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
Debug.Print (sht.Name)
Next
 
End Sub

 

[tech2]

Actually I'm embarrassed to say that you are correct the Book name is 'Report_6'.
I added the Windows("Report_6.xls").Activate, code and still got the same 'error 9 subscript out of range'.
The Copy file code works Great, it copies the workbook to the desktop folder. The code fails when it gets to renaming the sheet called 'Report_6'.

 

[Zaf]

The sheet needs to exist in your active workbook. If it doesn't exist then your code will fail at 'Sheets("Report").Select'.

When you get the error message type in your immediate window:

?Activeworkbook.Name

Again, you just need to be sure what workbook you are currently in...

Public Sub Test()
'Works fine, provided the sheet 'Prev Name' exists in your active workbook
Sheets("Prev Name").Select
Sheets("Prev Name").Name = "Another Name"
End Sub