PDA

View Full Version : Solved: Renaming Worksheet during Copy fails



YellowLabPro
07-30-2007, 02:46 PM
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

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


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

Bob Phillips
07-30-2007, 03:06 PM
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



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

YellowLabPro
07-30-2007, 03:18 PM
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?

YellowLabPro
07-30-2007, 03:21 PM
The answer to that one is NO! LOL

YellowLabPro
07-30-2007, 03:29 PM
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?


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

Bob Phillips
07-30-2007, 03:31 PM
What i mean is that i9n this code



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


when it gets to line 50 you are effectively saying



Worksheets("Sheet1").Sheets("Sheet1").Name = Left(wbkName.Name, Len(wbkName.Name) - 4)


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



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

YellowLabPro
07-30-2007, 03:53 PM
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.


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

YellowLabPro
07-30-2007, 04:08 PM
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:
ActiveSheet.Name = Left(wbkName.Name, Len(wbkName.Name) - 4) & Chr(32) & Format(Now, "m-dd-yy")



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

YellowLabPro
07-30-2007, 04:13 PM
One thing that might be worth looking at is that wbkName 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.

rory
07-30-2007, 04:16 PM
wbkName is a string, so you can't use wbkName.Name
Another good reason to explicitly type your variables... :)
Rory

YellowLabPro
07-30-2007, 04:18 PM
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?

YellowLabPro
07-30-2007, 04:26 PM
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.

rory
07-30-2007, 04:29 PM
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

rory
07-30-2007, 04:30 PM
Basically:
strFileName = Mid$(wbkname, instrrev(wbkName, "\") + 1)

rory
07-30-2007, 04:32 PM
And in an ideal world you would add a check for invalid characters and the maximum length of a sheet name (31 characters)

YellowLabPro
07-30-2007, 04:35 PM
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")

YellowLabPro
07-30-2007, 04:37 PM
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

is psuedo code

rory
07-30-2007, 04:39 PM
strFileName = Mid$(wbkname, instrrev(wbkName, "\") + 1)

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

rory
07-30-2007, 04:41 PM
And now I'm off to bed as it's almost 1am...
Good luck!
Rory

YellowLabPro
07-30-2007, 05:50 PM
Thanks Rory,
Well that did the trick.

Thanks Bob.