Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.Cou nt) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Overwrite Macro Change
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.Cou nt) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Overwrite Macro Change
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.Cou nt) 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.Cou nt) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Change Macro Help | Excel Discussion (Misc queries) | |||
Worksheet change macro for paste special | Excel Discussion (Misc queries) | |||
Worksheet change Macro Question | Excel Discussion (Misc queries) | |||
Worksheet change Macro part 2 | Excel Discussion (Misc queries) | |||
Overwrite Master sheet when using merge macro | Excel Programming |