I am using the code below (found in newsgroups) to open a closed file and
copy and rename a worksheet into a "Master" workbook. All works great
unless the name in Range("L3") is the same on more than 1 sheet. How and
where do place error control to append 01, 02, etc. to the end of
Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name = mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil

Hi Phil
try the following:

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
dim test_wks
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error

on error resume next
set test_wks =
worksheets(mybook.ActiveSheet.Range("L3").Value)
if err.number<0 then
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
else
msgbox "Could no rename worksheet"
end if
on error goto 0
Next i
End If
End With


--
Regards
Frank Kabel
Frankfurt, Germany


Phil Floyd wrote:
I am using the code below (found in newsgroups) to open a closed file
and copy and rename a worksheet into a "Master" workbook. All works
great unless the name in Range("L3") is the same on more than 1
sheet. How and where do place error control to append 01, 02, etc.
to the end of Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value With
ActiveSheet.UsedRange .Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil

Phil,

See my reply to the thread titled 'Renaming a Worksheet With Code Q' earlier
today, it covered the same topic.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Floyd" wrote in message
...
I am using the code below (found in newsgroups) to open a closed file and
copy and rename a worksheet into a "Master" workbook. All works great
unless the name in Range("L3") is the same on more than 1 sheet. How and
where do place error control to append 01, 02, etc. to the end of
Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name = mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil

Thanks Frank. The code gets through the error OK but it only copies the
sheet as Sheet1 and does not rename the sheet Range("L3") & 01, 02, etc.
Phil

"Frank Kabel" wrote in message
...
Hi Phil
try the following:

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
dim test_wks
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error

on error resume next
set test_wks =
worksheets(mybook.ActiveSheet.Range("L3").Value)
if err.number<0 then
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
else
msgbox "Could no rename worksheet"
end if
on error goto 0
Next i
End If
End With


--
Regards
Frank Kabel
Frankfurt, Germany


Phil Floyd wrote:
I am using the code below (found in newsgroups) to open a closed file
and copy and rename a worksheet into a "Master" workbook. All works
great unless the name in Range("L3") is the same on more than 1
sheet. How and where do place error control to append 01, 02, etc.
to the end of Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value With
ActiveSheet.UsedRange .Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil

Bob,
I have done several searches and have found no thread with the name you
mentioned. I'll give it a while and try again.
Phil

"Bob Phillips" wrote in message
...
Phil,

See my reply to the thread titled 'Renaming a Worksheet With Code Q'
earlier
today, it covered the same topic.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Floyd" wrote in message
...
I am using the code below (found in newsgroups) to open a closed file
and
copy and rename a worksheet into a "Master" workbook. All works great
unless the name in Range("L3") is the same on more than 1 sheet. How
and
where do place error control to append 01, 02, etc. to the end of
Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name = mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil

Hi
use Bob's suggestion in his other thread to increment the sheet number

--
Regards
Frank Kabel
Frankfurt, Germany

"Phil Floyd" schrieb im Newsbeitrag
...
Thanks Frank. The code gets through the error OK but it only copies
the
sheet as Sheet1 and does not rename the sheet Range("L3") & 01, 02,
etc.
Phil

"Frank Kabel" wrote in message
...
Hi Phil
try the following:

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
dim test_wks
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error

on error resume next
set test_wks =
worksheets(mybook.ActiveSheet.Range("L3").Value)
if err.number<0 then
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
else
msgbox "Could no rename worksheet"
end if
on error goto 0
Next i
End If
End With


--
Regards
Frank Kabel
Frankfurt, Germany


Phil Floyd wrote:
I am using the code below (found in newsgroups) to open a closed
file
and copy and rename a worksheet into a "Master" workbook. All
works
great unless the name in Range("L3") is the same on more than 1
sheet. How and where do place error control to append 01, 02,
etc.
to the end of Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value With
ActiveSheet.UsedRange .Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil

Sorry Phil, I forgot to mention that it is the thread is in the
Microsoft.Public.Excel.WorksheetFunctions newsgroup.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Floyd" wrote in message
...
Bob,
I have done several searches and have found no thread with the name you
mentioned. I'll give it a while and try again.
Phil

"Bob Phillips" wrote in message
...
Phil,

See my reply to the thread titled 'Renaming a Worksheet With Code Q'
earlier
today, it covered the same topic.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Floyd" wrote in message
...
I am using the code below (found in newsgroups) to open a closed file
and
copy and rename a worksheet into a "Master" workbook. All works great
unless the name in Range("L3") is the same on more than 1 sheet. How
and
where do place error control to append 01, 02, etc. to the end of
Range("L3") if that sheetname already exsists?

Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
' The line below is where I get the error
ActiveSheet.Name =
mybook.ActiveSheet.Range("L3").Value
With ActiveSheet.UsedRange
.Value = .Value
End With
mybook.Close
Next i
End If
End With

Thanks,
Phil