P
Phil Floyd
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
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