Help with VBA Code

[Todd G. McKissick]

I am trying to write a macro that will copy everything on spreadsheet, create a new workbook, and paste values and value formats. This code works when I step through it, but it does not work when I run the macro itself:

Sub Copysheet()

  ThisWorkbook.Activate
  Cells.Select
  Selection.Copy
  Dim NewCaseFile As Workbook
  Set NewCaseFile = Workbooks.Add
  With NewCaseFile
  Cells.Select
  Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False
  End With
  Application.CutCopyMode = False
  Range("A1").Select

End Sub

Anyone see what I'm doing wrong?

Todd

 

[p45cal]

Anyone see what I'm doing wrong?

What kind of module is the code in? (Sheet, Standard, ThisWorkbook).
In a standard code module try:

Sub Copysheet2()
ThisWorkbook.Activate
ActiveSheet.Copy  'creates a new workbook
'ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value 'use if no pivot tables present instead of the next 2 lines.
ActiveSheet.UsedRange.Copy 'the active sheet is now the new workbook.
ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[Todd G. McKissick]

After two hours, all I had to do was take out the "ThisWorkbook.Activate." This code now works:

Sub Copysheet()
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Dim NewCaseFile As Workbook
Set NewCaseFile = Workbooks.Add
With NewCaseFile
ActiveSheet.Select
Cells.Select
Selection.PasteSpecial 12
End With
Application.CutCopyMode = False
Range("A1").Select
End Sub