CODEWITHSUNDEEP
vbaexcel
Ganda
Ganda

Reputation: 175

Excel Macro: Copy and paste worksheet into another worksheet

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.

Upvotes: 0

Views: 3181

Answers (2)

RowanC
RowanC

Reputation: 1649

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

Upvotes: 2

Ganda
Ganda

Reputation: 175

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.

Upvotes: 1

Related Questions