Excel Macro: Copy and paste worksheet into another worksheet

0
votes

I've been able to make an exact copy of a worksheet and add it to the workbook with:

Sub Test()
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Detailed List")
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub

but I'm trying to have the copied worksheet paste over another worksheet. For example, I'm trying to copy the worksheet "Detailed List" and have it paste and overwrite "Testing Sheet", but all I'm able to do with the code above is make another worksheet. Any help would be great.

2
vbaexcel
I would just delete the original "Testing Sheet" first, then when you copy "Detailed List" rename it as "Testing Sheet" - CactusCake
Can I rename it in the macro? The only reason I want to do this is to be able to show the user, once he/she makes a change to the Detailed List, the value that used to be in that cell, so he/she can always reference it - Ganda

2 Answers

2
votes

You nearly had it, and your own solution works...however, in the efforts of completeness...

Sub Test()
    Dim ws1 As Worksheet
    Dim ws1Copy As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Detailed List")

    ws1.Copy ThisWorkbook.Sheets(Sheets.Count) 'we can't set the copied object directly to a varaiable, but it becomes the active sheet by default

    Set ws1Copy = ActiveSheet 'assign the newly copied activesheet to a variable

    Application.DisplayAlerts = False 'supress warnings, the delete method gives the user a warning
    Sheets("Testing Sheet").Delete 'delete the testing sheet
    Application.DisplayAlerts = True 'un-supress the warnings

    ws1Copy.Name = "Testing Sheet" 'change the name of the copied sheet to the testing sheet.
End Sub
1
votes

So I ended up doing this:

Worksheets("Detailed List").Range("A7").CurrentRegion.Copy
Worksheets("Detailed Copy").Range("A7").PasteSpecial

I first just copied the Detailed List before I started running the macro. Then I highlighted all the data points and titles, and overwrite the Copy. I needed to do this because I am making a macro that whenever someone changes a cell in the "Detailed List", I need to display the Old Value that used to be in the cell, so by having essentially two copies of the List, I can first make the comparisons, and then just copy the list over and over, so it automatically updates itself to any changes that have been made.