+ Reply to Thread
Results 1 to 14 of 14

Simple copy and paste macro

  1. 11-08-2012, 05:45 AM #1
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Post Simple copy and paste macro

    Hi Guys,

    I have found a macro I have been using on my worksheet for a while now,

    I basically copy a value from a fixed location (cell A1) to another cell which has a fixed column ("C") but a variable row.

    The row number is stored in cell A2

    So if cell A2 contained the value 23, I want to copy the contents of A1 to cell C23.


    I have been using this code

    PHP Code: 
    Range("A1").Copy Destination:=Range("C" & Range("A2").Value) 
    I would now like to paste the copyied cell to another sheet rather than in column "C"

    However I still want the range to be dictated by "A2"


    Can this be done?

    Thanks for your help
    Hernan
    Last edited by hernancrespo; 11-08-2012 at 05:46 AM. Reason: Rude post
    Reply With QuoteRegister To Reply
  2. 11-08-2012, 06:18 AM #2
    Sindhus
    Sindhus is offline
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Simple copy and paste macro

    What should be destination sheet name and column?
    Reply With QuoteRegister To Reply
  3. 11-08-2012, 06:21 AM #3
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Simple copy and paste macro

    Hi Sindhus,

    The destination sheet name should be "Week 1" and the column D

    Sorry I should of thought of this when I posted!
    Reply With QuoteRegister To Reply
  4. 11-08-2012, 06:25 AM #4
    Sindhus
    Sindhus is offline
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Simple copy and paste macro

    Try this 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 11-08-2012, 06:29 AM #5
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Simple copy and paste macro

    Alas! no

    I get this error?

    111111.JPG

    "Run-time error "1004"

    copy method of range class failed"

    When I click Debug it highlights the whole line
    Last edited by hernancrespo; 11-08-2012 at 06:50 AM.
    Reply With QuoteRegister To Reply
  6. 11-08-2012, 06:56 AM #6
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Simple copy and paste macro

    Which sheet is the cell A2 that you are referencing on and what is the name of the sheet you are copying from?
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
    Reply With QuoteRegister To Reply
  7. 11-08-2012, 07:01 AM #7
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Simple copy and paste macro

    Hi WasWodge,

    The sheet I am trying to reference from is called "Diary" and the the reference is on this sheet

    So "A2" is on the "Diary" Sheet

    Thanks in advance

    Jon
    Reply With QuoteRegister To Reply
  8. 11-08-2012, 07:04 AM #8
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Simple copy and paste macro

    Try
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 11-08-2012, 07:30 AM #9
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Simple copy and paste macro

    Hi WasWodge,

    Unfortunately I am getting a different error message-

    "Run-time error "1004"
    Application defined or object defined error"

    Thanks for you efforts though!

    I'm sure there is a simple solution, but can't for the life of me figure out what it is!
    Reply With QuoteRegister To Reply
  10. 11-08-2012, 07:35 AM #10
    Sindhus
    Sindhus is offline
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Simple copy and paste macro

    Can you upload your workbook?
    Reply With QuoteRegister To Reply
  11. 11-08-2012, 07:50 AM #11
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Simple copy and paste macro

    Hi Guys,

    I have had to heavily strip this workbook down- but hopefully it will show you what I am intending to do

    I want to take what is written in cell B7

    and cut and paste it in Week 1 in the same position in the diary- so in theory I can fill the diary for the day and then press a button linked to this macro which will copy and paste it in the relevant diary. And then run a macro which will show what is written in that cell. So in effect making a Two way cell.

    The reason that the value has to be variable is that I will do a look-up up on the cell reference for each different contractor- which will be selected by the user.

    my theory makes sense in my head! apologies if this is a mad mans scrawlings!

    thanks

    hernanMonth Diary Strip.xls
    Reply With QuoteRegister To Reply
  12. 11-08-2012, 08:06 AM #12
    Sindhus
    Sindhus is offline
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Simple copy and paste macro

    It runs perfectly. I dont find any error.

    Btw i just noticed that your workbook_sheetchange code will never enter the first if condition. If you have Ucase(Sh.Name) which will return "DIARY". It will not equal to "Diary".
    Reply With QuoteRegister To Reply
  13. 11-08-2012, 08:13 AM #13
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Simple copy and paste macro 

    Please Login or Register  to view this content.
    But the poster will get the error if they don't put a number in cell C32 and tries running the code
    Reply With QuoteRegister To Reply
  14. 11-08-2012, 08:16 AM #14
    hernancrespo
    hernancrespo is offline
    Registered User
    Join Date
    10-04-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Simple copy and paste macro

    Thanks Guys

    it is working now!

    i had the code pasted in the sheet and not the workbook- it seems to have resolved the issue!

    thanks alot for your help!
    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)

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