VBA Code for Pasting Contents

[Curt]

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

 

[Rick Rothstein]

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.

 

[Javed]

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)










- 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.

 

[Curt]

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.