+ Reply to Thread
Results 1 to 11 of 11

VBA Copy from another sheet & paste in cell where the formula is

  1. 01-16-2014, 06:01 AM #1
    ch_abs
    ch_abs is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2003=7
    Posts
    25

    VBA Copy from another sheet & paste in cell where the formula is

    Hi,

    I'm trying to copy a number from a worksheet 2, and paste it into the cell in worksheet 1 where I am inserting the formula.

    This is what I have so far:
    Please Login or Register  to view this content.
    Currently, when I run it, it tries to paste the value into a random cell in worksheet 2, and when it does it just shows #VALUE!

    Thanks in advance

    UPDATE:

    I currently have the code below which returns a #VALUE! error now into the correct cell:
    Please Login or Register  to view this content.
    Can I use ActiveCell as i have? :S
    Last edited by ch_abs; 01-16-2014 at 06:28 AM.
    Reply With QuoteRegister To Reply
  2. 01-16-2014, 10:09 AM #2
    Swindon Dominic
    Swindon Dominic is offline
    Registered User
    Join Date
    01-24-2012
    Location
    Swindon, England
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Hi

    I think your main problem is that CellCopy is an integer number whereas you want CellCopy to be a cell reference on worksheet 2. Are you trying to pick up the value for a numbered row in a given column?

    Regards

    Dominic
    Reply With QuoteRegister To Reply
  3. 01-16-2014, 10:16 AM #3
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Copy from another sheet & paste in cell where the formula is

    A function does not copy (Action), but return VALUES only, unless calling it. You need to change it to sub
    Reply With QuoteRegister To Reply
  4. 01-16-2014, 10:56 AM #4
    ch_abs
    ch_abs is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2003=7
    Posts
    25

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Hi,

    This is going to be part of something bigger but for now I want to copy a given value in Sheet2 ("Mappings") and paste it into the Active Cell of Sheet1 ("Main").

    Thanks for the help so far...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 01-16-2014, 10:57 AM #5
    ch_abs
    ch_abs is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2003=7
    Posts
    25

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Hi,

    This is going to be part of something bigger but for now I want to copy a given value in Sheet2 ("Mappings") and paste it into the Active Cell of Sheet1 ("Main").

    Thanks for the help so far...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 01-16-2014, 11:00 AM #6
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Since you are copying in to active cell, you need to put the cursor on the active cell when you run the code.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 01-16-2014, 11:14 AM #7
    ch_abs
    ch_abs is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2003=7
    Posts
    25

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Quote Originally Posted by AB33 View Post
    Since you are copying in to active cell, you need to put the cursor on the active cell when you run the code.

    Please Login or Register  to view this content.
    I have my cursor on the active cell and that is the one I have selected. When using your code I get the error "Object doesn't support this property or method".

    Thanks for your patience...new to this, this week!
    Reply With QuoteRegister To Reply
  8. 01-16-2014, 11:20 AM #8
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Has the active cell got a name? Where is the active cell in sheet main? is in in A1, B1, etc?
    Reply With QuoteRegister To Reply
  9. 01-16-2014, 11:24 AM #9
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Assuming you want to copy in to A1,

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 01-16-2014, 11:26 AM #10
    ch_abs
    ch_abs is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2003=7
    Posts
    25

    Re: VBA Copy from another sheet & paste in cell where the formula is

    It's "P2" in Sheet Main.

    I really want to avoid setting the paste destination to a hard coded place because later on I want to use the copy&paste in a Function...this is assuming if I put "P2" in as the paste destination it would be an absolute cell reference?
    Reply With QuoteRegister To Reply
  11. 01-16-2014, 11:30 AM #11
    ch_abs
    ch_abs is offline
    Registered User
    Join Date
    01-08-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2003=7
    Posts
    25

    Re: VBA Copy from another sheet & paste in cell where the formula is

    Quote Originally Posted by AB33 View Post
    Assuming you want to copy in to A1,

    Please Login or Register  to view this content.
    That worked great thanks when I put in an actual cell range!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to copy a cell range and paste to a new sheet 'n' times where 'n' defined by formula
    By staminaboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:39 AM
  2. How to copy data with formula and paste another sheet as paste spl -value only
    By sumesh56 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-14-2013, 09:03 PM
  3. Macro to update database, Match Cell value from Sheet 1 in Sheet 2 then copy and paste
    By Zawoooo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-03-2012, 03:10 AM
  4. Formula to Copy and paste special from sheet 5 to next open column sheet 2 and 3
    By GeorgeD in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 05-24-2010, 05:17 PM
  5. Macro to search sheet 1 and copy paste to chosen cell in another sheet
    By saint1970 in forum Excel General
    Replies: 2
    Last Post: 10-15-2009, 10:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1