Macro to copy worksheet

[Guest]

I have a Summary worksheet that updates daily using several vlookup formulas.
I created a macro that would archive this information. In the macro, I
first make a copy of the summary worksheet. Then on the copy, I copy/paste
special the values and formats onto the worksheet to make sure that I get
only the values and not the vlookup formulas.

The problem is that when I go to run the macro the next day, it creates a
copy of the wrong worksheet. I need it to create a copy of the Summary
worksheet. Here is the code for the macro.


Sub Archive()
'
' Archive Macro
' Macro recorded 6/12/2007 by Nekiah V. Jackson
'
' Keyboard Shortcut: Ctrl+b
'
Sheets("Summary").Select
Sheets("Summary").Copy Before:=Sheets(5)
Sheets("Summary (2)").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D11").Select
End Sub

 

[Dave Peterson]

Your code looks like it always copies the worksheet named Summary.

Maybe you meant that it it always copy|pastespecial values against the wrong
sheet.

Option Explicit
Sub Archive2()

with activeworkbook
'put it at the end?
.Sheets("Summary").Copy _
After:=.Sheets(.sheets.count)
end with

with activesheet 'the sheet just created
.cells.copy
.cells.pastespecialPaste:=xlPasteValues
end with

End Sub

This doesn't rely on the name of the new sheet--it just uses the newly activated
sheet (the one that was just created by the .copy).

 

[Dave Peterson]

Oops. Dropped a space character:
..cells.pastespecialPaste:=xlPasteValues
should be:
..cells.pastespecial Paste:=xlPasteValues