Worksheet Overwrite Macro Change

Hi All,

I am using this macro to automatically copy worksheets from other files to this file.

This has the code for automatically naming the new worksheets with the count number when a worksheet of the same name already exists in the file.

I want to take off this feature. I want the macro to overwrite the sheets without uniquely naming the new sheets

I tried taking off the two lines of code. . but it gives me errors.

Can somebody help me. .

Here is the code.


Option Explicit

Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Documents and Settings\Desktop"
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I have had that problem before too and while there is probably a better way
to get around it, i simply searched the workbook for a sheet with a given
name, deleted the sheet if it was present, then was free to name the sheet
whatever. For instance:

dim i as integer
' Deletes worksheets w/ same name

Application.DisplayAlerts = False
For i = 1 To Sheets.Count - 1
If Sheets(i).name = "NAME" Then
Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = True

based on your situation, i would recommend loading the name of the sheet
into a string, testing for that, deleting, then move/copying the sheet.

Hope that helps.


"biojunkie" wrote:

>
> Hi All,
>
> I am using this macro to automatically copy worksheets from other files
> to this file.
>
> This has the code for automatically naming the new worksheets with the
> count number when a worksheet of the same name already exists in the
> file.
>
> I want to take off this feature. I want the macro to overwrite the
> sheets without uniquely naming the new sheets
>
> I tried taking off the two lines of code. . but it gives me errors.
>
> Can somebody help me. .
>
> Here is the code.
>
>
> Option Explicit
>
> Sub CombineFiles()
> Dim Path As String
> Dim FileName As String
> Dim Wkb As Workbook
> Dim WS As Worksheet
>
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> Path = "C:\Documents and Settings\Desktop"
> FileName = Dir(Path & "\*.xls", vbNormal)
> Do Until FileName = ""
> Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
> For Each WS In Wkb.Worksheets
> WS.Copy
> After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
> Next WS
> Wkb.Close False
> FileName = Dir()
> Loop
> Application.EnableEvents = True
> Application.ScreenUpdating = True
>
> End Sub
>
>
> --
> biojunkie
> ------------------------------------------------------------------------
> biojunkie's Profile: http://www.excelforum.com/member.php...o&userid=17859
> View this thread: http://www.excelforum.com/showthread...hreadid=391906
>
>

How about just deleting before you copy?

For Each WS In Wkb.Worksheets
on error resume next
application.displayalerts = false
thisworkbook.sheets(ws.name).delete
application.displayalerts = true
on error goto 0
WS.Copy _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS

The "on error" stuff will let the code continue if there isn't a worksheet with
that name. The .displayalerts stops the "are you sure" prompt.



biojunkie wrote:
>
> Hi All,
>
> I am using this macro to automatically copy worksheets from other files
> to this file.
>
> This has the code for automatically naming the new worksheets with the
> count number when a worksheet of the same name already exists in the
> file.
>
> I want to take off this feature. I want the macro to overwrite the
> sheets without uniquely naming the new sheets
>
> I tried taking off the two lines of code. . but it gives me errors.
>
> Can somebody help me. .
>
> Here is the code.
>
> Option Explicit
>
> Sub CombineFiles()
> Dim Path As String
> Dim FileName As String
> Dim Wkb As Workbook
> Dim WS As Worksheet
>
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> Path = "C:\Documents and Settings\Desktop"
> FileName = Dir(Path & "\*.xls", vbNormal)
> Do Until FileName = ""
> Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
> For Each WS In Wkb.Worksheets
> WS.Copy
> After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
> Next WS
> Wkb.Close False
> FileName = Dir()
> Loop
> Application.EnableEvents = True
> Application.ScreenUpdating = True
>
> End Sub
>
> --
> biojunkie
> ------------------------------------------------------------------------
> biojunkie's Profile: http://www.excelforum.com/member.php...o&userid=17859
> View this thread: http://www.excelforum.com/showthread...hreadid=391906

--

Dave Peterson