VBA, Excel-Copy value only of cells from one worksheet to another worksheet

[Kim]

Goal: Copy the value only (not formulas) of a range of cells (B2:Y34) from Worksheet 6 to Worksheet7

Existing VBA code:

Sub Copy_Data()
Application.ScreenUpdating = False
Worksheets("Sheet6").Range("B2:Y34").Value = ActiveSheet("Sheet7").Range("B2:Y34").Value
Application.ScreenUpdating = True
End Sub

If anyone would be able to point me in the right direction I would greatly appreciate it. I am having trouble pinpointing and resolving the gliche.

[Ron Weller]

I recorded a macro and did the steps you wanted. This is what I got:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/30/2009 by Ronald A Weller
'
  Range("B2:Y34").Select
  Selection.Copy
  Sheets("Sheet5").Select
  Range("B2").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

[Ron Weller]

I tested your routine also and with a few changes it will work also; but because you are not doing a copy and paste you can't use undo to cancel the copy.
Here is the modified version of your routine:

Sub Copy_Data()
  Application.ScreenUpdating = False
  Worksheets("Sheet7").Range("B2:Y34").Value = Worksheets("Sheet6").Range("B2:Y34").Value
  Application.ScreenUpdating = True
End Sub

Notice here I changed your ActiveWorksheet to Worksheets, The ActiveWorksheet object is reference to the currently Active worksheet not a collection of worksheets, so ActiveWorksheet("Name") won't work.

Also you had your object references backwords; which would have overitten the values in worksheet 6 with the ones in worksheet 7. So I changed the object references so that the worksheet 7 object was assigned the values from the worksheet 6 object. Not the other way around.