copy and paste

[Blake 7]

Hi Guys, hope all is well.

As you will see from the code below I am trying to copy the contents of a worksheet to a new sheet.

However, I am having trouble getting the pictures to copy over. I have attached a dummy sample. Press Ctrl Q to run the macro.
It works fine without the line "Worksheets("Winback Current Report").objpic.Copy" but i really need the images.

Any ideas please?

[AB33]

Blake 7
You have two copies in consecutives
You are copying cells and without pasting and you copying another one

Please Login or Register  to view this content.

[Blake 7]

Hiya,

Thanks, in the end I got it to work like this. Im not sure that its brilliant coding but it seems to work.

If anyone can see a smarter way please share.

Sub NewSheetFromTemplate()
Dim sName As String

sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2)
If sName = "" Then Exit Sub
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sName

Worksheets("Winback Current Report").Cells.Copy
Worksheets(sName).Range("A1").PasteSpecial (xlPasteValues)
Worksheets(sName).Range("A1").PasteSpecial (xlPasteFormats)
Worksheets("Winback Current Report").Shapes("Pic1").Copy
Worksheets(sName).Range("A1").PasteSpecial
Worksheets("Winback Current Report").Shapes("Pic2").Copy
Worksheets(sName).Range("I1").PasteSpecial


End Sub