Copy worksheet, rename, merged cell problem??
 

Hi all, i have some code below that i have put together to copy a
worksheet using the sheet name and some text and the value in A1 i
havent got it to run right the way through yet it stops at the
Selection.PasteSpecial line in the code.....any ideas?

Regards,
Simon

Sub Saveweek()
Dim OriginalSheet
Application.ScreenUpdating = False
Application.EnableEvents = False
OriginalSheet = ActiveSheet.Name
Range("A1:N239").Select
Application.CutCopyMode = False
Selection.Copy
Sheets(OriginalSheet).Select
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:n").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWindow.DisplayGridlines = False
ActiveSheet.Select
ActiveSheet.Name = Sheet.Name & "Starting " & Range("A1").Text
Sheets(OriginalSheet).Select
Range("A1").Select
Application.ScreenUpdating = True
Application.EnableEvents = False
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=552545

Copy worksheet, rename, merged cell problem??
 

Simon,

At the end of the line "Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone," you need to place _ to get this part to work

Alan

Simon Lloyd wrote:
Hi all, i have some code below that i have put together to copy a
worksheet using the sheet name and some text and the value in A1 i
havent got it to run right the way through yet it stops at the
Selection.PasteSpecial line in the code.....any ideas?

--
Message posted via http://www.officekb.com

Copy worksheet, rename, merged cell problem??
 

Alan thanks for the reply but as far as i can see the modification you
suggested is present! after "Skip Blanks"

Whilst i was tired last night i forgot to mention that it stops at that
part of the code because i have merged cells and it says the area it is
getting pasted to should be the same size?

Any other ideas?

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=552545