I would like a VBA code that pastes the contents of a duplicate sheet into
it's corresponding orginal sheet.

For example, paste of "Sheet1 (2)" into "Sheet1".

Please do this for all sheets with duplicate names. You do not have to
worry about there ever being more than one duplicate. There will never be a
"Sheet1 (3)".

After this is done, I would like a separate code that deletes all duplicate
sheets.

For example, after pasting "Sheet1 (2)" into "Sheet1", delete "Sheet1 (2).


thanks,

Curt

Is the data from "Sheet1 (2)" replacing the data on "Sheet1" in its
entirety? If so, why copy/paste? Why not simply delete "Sheet1" and rename
"Sheet1 (2)" to "Sheet1"? Something like this maybe...

Sub ProcessDuplicateSheets()
Dim WS As Worksheet, BaseName As String
Application.DisplayAlerts = False
For Each WS In Worksheets
If WS.Name Like "* (2)" Then
BaseName = Split(WS.Name, " (2)")(0)
Worksheets(BaseName).Delete
WS.Name = BaseName
End If
Next
Application.DisplayAlerts = True
End Sub

Note: The changes made by this macro cannot be undone, so make sure to test
it out on a copy of your workbook.

--
Rick (MVP - Excel)



"Curt" wrote in message
...
I would like a VBA code that pastes the contents of a duplicate sheet into
it's corresponding orginal sheet.

For example, paste of "Sheet1 (2)" into "Sheet1".

Please do this for all sheets with duplicate names. You do not have to
worry about there ever being more than one duplicate. There will never be
a
"Sheet1 (3)".

After this is done, I would like a separate code that deletes all
duplicate
sheets.

For example, after pasting "Sheet1 (2)" into "Sheet1", delete "Sheet1 (2).


thanks,

Curt

On Apr 27, 8:17*pm, "Rick Rothstein"
wrote:
Is the data from "Sheet1 (2)" replacing the data on "Sheet1" in its
entirety? If so, why copy/paste? Why not simply delete "Sheet1" and rename
"Sheet1 (2)" to "Sheet1"? Something like this maybe...

Sub ProcessDuplicateSheets()
* Dim WS As Worksheet, BaseName As String
* Application.DisplayAlerts = False
* For Each WS In Worksheets
* * If WS.Name Like "* (2)" Then
* * * BaseName = Split(WS.Name, " (2)")(0)
* * * Worksheets(BaseName).Delete
* * * WS.Name = BaseName
* * End If
* Next
* Application.DisplayAlerts = True
End Sub

Note: The changes made by this macro cannot be undone, so make sure to test
it out on a copy of your workbook.

--
Rick (MVP - Excel)

"Curt" wrote in message

...



I would like a VBA code that pastes the contents of a duplicate sheet into
it's corresponding orginal sheet.

For example, paste of "Sheet1 (2)" into "Sheet1".

Please do this for all sheets with duplicate names. *You do not have to
worry about there ever being more than one duplicate. *There will never be
a
"Sheet1 (3)".

After this is done, I would like a separate code that deletes all
duplicate
sheets.

For example, after pasting "Sheet1 (2)" into "Sheet1", delete "Sheet1 (2).

thanks,

Curt- Hide quoted text -

- Show quoted text -

The code is very nice.

One addition:
Normally macro actions can not be Undone.if wrongly run then do not
press "Save" button "close" the workbook and select "No" in the alert
dialog which appers after closing a workbook.

Rick, thanks for the response.

The problem that I am having is that I have formulas that refrence the
original sheet1, sheet2, sheet3.

If I change the name of sheet1 (2) to sheet1, I am left with a cell refrence.

"Rick Rothstein" wrote:

Is the data from "Sheet1 (2)" replacing the data on "Sheet1" in its
entirety? If so, why copy/paste? Why not simply delete "Sheet1" and rename
"Sheet1 (2)" to "Sheet1"? Something like this maybe...

Sub ProcessDuplicateSheets()
Dim WS As Worksheet, BaseName As String
Application.DisplayAlerts = False
For Each WS In Worksheets
If WS.Name Like "* (2)" Then
BaseName = Split(WS.Name, " (2)")(0)
Worksheets(BaseName).Delete
WS.Name = BaseName
End If
Next
Application.DisplayAlerts = True
End Sub

Note: The changes made by this macro cannot be undone, so make sure to test
it out on a copy of your workbook.

--
Rick (MVP - Excel)



"Curt" wrote in message
...
I would like a VBA code that pastes the contents of a duplicate sheet into
it's corresponding orginal sheet.

For example, paste of "Sheet1 (2)" into "Sheet1".

Please do this for all sheets with duplicate names. You do not have to
worry about there ever being more than one duplicate. There will never be
a
"Sheet1 (3)".

After this is done, I would like a separate code that deletes all
duplicate
sheets.

For example, after pasting "Sheet1 (2)" into "Sheet1", delete "Sheet1 (2).


thanks,

Curt

.