Macro to copy & paste entire sheet & objects

tobyz95

Board Regular
Joined
Jul 28, 2012
Messages
190
Hi ALL, I have been working on this tooo long without the results I want :oops:

I need to copy and paste a worksheet that also has objects/shapes to a new worksheet and have the objects/shapes end up in right space and size of the correct cells.

So far, I have not been able to do both in one macro and have the shapes/objects end up in the right place and size.

If I use right-click on tab to copy & paste...the objects/shapes do not come out right

I have tried a guzillion combos, they don't end up in the right place within the right cell.

Any :rolleyes: , please(?)
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
so

this sort of thing doesn't work?

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(3)

or this

Sheets("Sheet2").Select
Sheets("Sheet2").Copy Before:=Workbooks("Book1").Sheets(1)
 
Upvote 0
so

this sort of thing doesn't work?

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(3)

or this

Sheets("Sheet2").Select
Sheets("Sheet2").Copy Before:=Workbooks("Book1").Sheets(1)

Thanks for responding. No because I am copying from a specific sheet to another specific sheet, not creating a new one.
 
Upvote 0
So your requirement is to copy a range of cells and shapes from one existing sheet to another existing sheet, I assume that on the target sheet there are items that will not be over written by the copy ?
 
Upvote 0
Not just a range of cells and shapes, the whole sheet including shapes, and target sheet will be blank. My workbook has 13 sheets. One for each month, and one is a template I created. So I am copying the template which happens to have objects/shapes on it, to one of the month sheets that I have already cleared and is blank.
 
Upvote 0
Try this

it asks which month you wish to reset, ( attempts to prompt the probable month )

ask where the sheet should be placed after which sheet

deletes the target, copies the template to the requested position, renames the copy as the target

Code:
Sub Macro1()
'
' Macro1 Macro
'
Dim SheetToReplace As String
Dim SheetAfter As String
    
On Error Resume Next
SheetToReplace = InputBox("Name sheet to replace with Template", "Replace Sheet with copy of Template", Format(Now(), "MMMM"))
    
SheetAfter = InputBox("Name sheet to insert replacement After", "Replace Sheet with copy of Template", IIf(Month(Now()) = 1, "Template", Format("01/" & Month(Now()) - 1 & "/" & Year(Now()), "MMMM")))
    
    
    
    Sheets(SheetToReplace).Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("Template").Select
    
    If SheetAfter = "" Then
        Sheets("Template").Copy
    Else
        Sheets("Template").Copy After:=Sheets(SheetAfter)
    End If
    
    ActiveSheet.Name = SheetToReplace
    
    
    Application.DisplayAlerts = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top