Macro to copy & paste entire sheet & objects

tobyz95

Board Regular
Joined
Jul 28, 2012
Messages
183
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(?)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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)
 

tobyz95

Board Regular
Joined
Jul 28, 2012
Messages
183
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.
 

tobyz95

Board Regular
Joined
Jul 28, 2012
Messages
183

ADVERTISEMENT

so not the whole sheet?

( because iif it is the whole sheet then delete target and create copy, rename sheet )

Yes, I am copying the whole sheet, but I want to paste it and the objects on it to another existing sheet.
 

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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 ?
 

tobyz95

Board Regular
Joined
Jul 28, 2012
Messages
183

ADVERTISEMENT

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.
 

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,683
Messages
5,597,526
Members
414,152
Latest member
ReservoirDodds

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
Top