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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

  1. #1
    Join Date
    Mar 2009
    Posts
    1

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

    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.

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    I recorded a macro and did the steps you wanted. This is what I got:
    Code:
    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

  3. #3
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    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:
    Code:
    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.

Similar Threads

  1. can't copy from Word document VBA excel form
    By thegeeber in forum VB Classic
    Replies: 11
    Last Post: 04-03-2009, 11:52 PM
  2. Loop to copy rows and paste into worksheet
    By damien_carr in forum VB Classic
    Replies: 0
    Last Post: 01-29-2009, 04:29 PM
  3. Replies: 0
    Last Post: 01-06-2009, 09:44 AM
  4. Replies: 0
    Last Post: 05-15-2008, 04:24 PM
  5. Copy From HTML to Excel
    By zakkar in forum .NET
    Replies: 3
    Last Post: 06-25-2007, 07:30 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center