Solved: Renaming Worksheet during Copy fails

Copying the worksheet from Book1.xls to Masterworkbook.xls after Summary sheet. Original worksheet name is Sheet1, I wish to rename this worksheet to the original workbook name, & add the current date to the end.

Sheet1 would become Book1_7-30-07

[vba].Sheets("Sheet1").Name = Left(wbkName.Name, Len(wbkName.Name)-4) [/vba]is erroring.
The error is Object Required.

[vba]
Workbooks.Open Filename:=wbkName, IgnoreReadOnlyRecommended:=True
'open the workbook concerned
With ActiveWorkbook
With Worksheets("Sheet1")
'do your stuff here, change the sheetname as required

.Copy after:=Wsm
.Sheets("Sheet1").Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
End With
.Close savechanges:=True
'close the workbook (saving any changes)
End With
Application.ScreenUpdating = True
End Sub
[/vba]

I am not sure which you are trying to rename, Sheet1 or the new sheet, but the problem is caused by trying to double qualify the workhseet object

[vba]

With ActiveWorkbook
With Worksheets("Sheet1")
'do your stuff here, change the sheetname as required

.Copy after:=Wsm
.Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
End With
.Close savechanges:=True
'close the workbook (saving any changes)
End With
[/vba]

I am trying to rename the new sheet being copied over to the target book: Masterworkbook.xls which after the sheet is copied over, is Sheet1.

I do not wish to rename the original Sheet name in the Source workbook, but in fact leave it as is.

If your edit to my code was supposed to fix, it did not, I still get the Object Required error.
I do not fully understand the double qualifying point you are trying to make to me.
Do I need to tell it this is to now be a string, or variant, Dim wkbName as string or variant?

The answer to that one is NO! LOL

Bob,
I am also in the wrong workbook.
I just did a manual rename and it is renaming the sheet in the Source workbook, not in the Target workbook. So that is why it is probably confusing you what I am trying to do.

Here is my code for the manual rename. It does rename Test7-30-07, but in Book1.xls. Clearer?

[VBA]
Workbooks.Open Filename:=wbkName, IgnoreReadOnlyRecommended:=True
'open the workbook concerned
With ActiveWorkbook
With Worksheets("Sheet1")
'do your stuff here, change the sheetname as required

.Copy after:=Wsm
.Name = "test" & Format(Now, "m-dd-yy")
.Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
End With
[/VBA]

What i mean is that i9n this code

[vba]

10 Workbooks.Open Filename:=wbkName, IgnoreReadOnlyRecommended:=True
'open the workbook concerned
20 With ActiveWorkbook
30 With Worksheets("Sheet1")
'do your stuff here, change the sheetname as required

40 .Copy after:=Wsm
50 .Sheets("Sheet1").Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
60 End With
70 .Close savechanges:=True
'close the workbook (saving any changes)
80 End With
90 Application.ScreenUpdating = True
End Sub
[/vba]

when it gets to line 50 you are effectively saying

[vba]

Worksheets("Sheet1").Sheets("Sheet1").Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
[/vba]

because line 30 is setting the current object scope to Worksheets("Sheet1"), and then line 50 tries to access Sheets("Sheet1") within the current object scope.

It worked in my small tst, maybe there is other code doing something I am not seeing.

If you want to rename the new sheet, you would use

[vba]

Workbooks.Open Filename:=wbkName, IgnoreReadOnlyRecommended:=True
'open the workbook concerned
With ActiveWorkbook
With .Worksheets("Sheet1")
'do your stuff here, change the sheetname as required

.Copy After:=Wsm
ActiveSheet.Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
End With
.Close savechanges:=True
'close the workbook (saving any changes)
End With
Application.ScreenUpdating = True
[/vba]

Thanks very much, I will study through what you posted.
Just so you have it, here is all my code- Just so you know, this is still very very rough. I am working through each item.

[vba]
Option Explicit
Sub PrepareFiles()
Dim Master As Workbook
Dim WBm As Workbook
Dim wbks As Workbook
Dim Wks As Long, i As Long
Dim LRowM As Long
Dim vFName As Variant
'Workbooks Directory
Const FileDirectory As String = "C:\Documents and Settings\Doug\Desktop\ForEach\"
Set WBm = Workbooks("MasterWorkbook.xls")
With Application.FileSearch
.NewSearch
.LookIn = FileDirectory
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
'workbooks found
If .Execute > 0 Then

'loop through each found workbook / 'pass workbook fullname to your routine

For Each vFName In .FoundFiles
If vFName <> ThisWorkbook.Name Then GetReportData vFName


'Copy Worksheets from Source books to target
'Ws("Sheet1").Copy After:=Wsm

Next
Else
MsgBox "There were no Excel files found."
End If
End With
End Sub
Sub GetReportData(wbkName)
Dim Wsm As Worksheet
Dim Ws As Worksheet
Dim WB As String
Dim Sheet1 As String
Set Wsm = Workbooks("MasterWorkbook.xls").Worksheets("Summary")
Workbooks.Open Filename:=wbkName, IgnoreReadOnlyRecommended:=True

With ActiveWorkbook
With Worksheets("Sheet1")
'do your stuff here, change the sheetname as required

.Copy after:=Wsm
With ActiveSheet
.Name = wbkName.Name & Chr(32) & Format(Now, "m-dd-yy")
End With
'.Name = "test" & "_" & MonthName(Month) & "-" & Day(Date) & "-" & Year(Date)
.Name = Left(wbkName.Name, Len(wbkName.Name) - 4)
' 'Sheets("Sheet1 (3)").Name = "test"
End With
ActiveSheet.Name = wbkName
.Close savechanges:=True
End With
Application.ScreenUpdating = True
End Sub

[/vba]

I get you about the double qualifying.
I thought you were refering to the variable/argument wbkName.

Sorry about the last ugly post.
I have cleaned it up to get rid of all the junk.
The line you gave me I did change, but it is still looking for an object.
I have updated it to include the system date when saved, it works if I use a string in front rather than the variable.

Problem Line:
[VBA]ActiveSheet.Name = Left(wbkName.Name, Len(wbkName.Name) - 4) & Chr(32) & Format(Now, "m-dd-yy")[/VBA]


[VBA]
Option Explicit
Sub PrepareFiles()
Dim Master As Workbook
Dim WBm As Workbook
Dim wbks As Workbook
Dim Wks As Long, i As Long
Dim LRowM As Long
Dim vFName As Variant
Const FileDirectory As String = "C:\Documents and Settings\Doug\Desktop\ForEach\"
Set WBm = Workbooks("MasterWorkbook.xls")
With Application.FileSearch
.NewSearch
.LookIn = FileDirectory
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For Each vFName In .FoundFiles
If vFName <> ThisWorkbook.Name Then GetReportData vFName

Next
Else
MsgBox "There were no Excel files found."
End If
End With
End Sub
Sub GetReportData(wbkName)
Dim Wsm As Worksheet
Dim Ws As Worksheet
Dim WB As String
Dim Sheet1 As String
Set Wsm = Workbooks("MasterWorkbook.xls").Worksheets("Summary")
Workbooks.Open Filename:=wbkName, IgnoreReadOnlyRecommended:=True
With ActiveWorkbook
With Worksheets("Sheet1")

.Copy after:=Wsm
ActiveSheet.Name = Left(wbkName.Name, Len(wbkName.Name) - 4) & Chr(32) & Format(Now, "m-dd-yy")
End With
End With

End Sub
[/VBA]

One thing that might be worth looking at is that [VBA]wbkName[/VBA] is shown as "C:\Documents and Settings\Doug\Desktop\ForEach\Book1.xls" in the locals window. Could this have anything to do w/ it?

I of course only want the file name, not the entire path.

wbkName is a string, so you can't use wbkName.Name
Another good reason to explicitly type your variables... :)
Rory

Hopefully I am not misunderstanding something, if so clarification is needed.
Is not wbkName the variable for the workbook name Book1.xls, making it a name?

Rory,
I followed your suggestion, I removed the .name after wbkName, it does not hang up on the object required, but does now try to rename the sheet the entire path name:
"C:\Documents and Settings\Doug\Desktop\ForEach\Book1.xls"
I am still of the opinion I need to get the filename from the variable.
But there may be some error in my understanding of that. But Xld said it ran for him. So-- maybe it is something else I do not see.

wbkName is a string variable, not a Workbook object. It has no properties or methods, so you cannot use wbkName.Name
To get the filename you can use InStrRev to get the position of the last backslash in the string then use the Mid function to return the filename. (You could also use Split but as you only want one portion, that's a bit unnecessary)
Regards,
Rory

Basically:
[VBA]strFileName = Mid$(wbkname, instrrev(wbkName, "\") + 1)[/VBA]

And in an ideal world you would add a check for invalid characters and the maximum length of a sheet name (31 characters)

Hate to poach-
but I need to get on w/ this. This is for the poster last week I am helping about combining files into one workbook/sheet, can you help me out w/ this line-
I need then to use your approach and combine it w/ this line-
ActiveSheet.Name = Left(wbkName, Len(wbkName) - 4) & Chr(32) & Format(Now, "m-dd-yy")

[VBA]
With ActiveWorkbook
With Worksheets("Sheet1")

.Copy after:=Wsm
ActiveSheet.Name = strfilename = mid$(wbkname, instrrev(wbkname, "\")+1 and _
Left(wbkName, Len(wbkName) - 4) & Chr(32) & Format(Now, "m-dd-yy")
End With
[/VBA]
is psuedo code

[vba]strFileName = Mid$(wbkname, instrrev(wbkName, "\") + 1)

ActiveSheet.Name = Left$(Left$(strFileName, Len(strFileName) - 4), 23) & Chr(32) & Format(Now, "m-dd-yy")
[/vba]

And now I'm off to bed as it's almost 1am...
Good luck!
Rory

Thanks Rory,
Well that did the trick.

Thanks Bob.