Pasting to a location specified within an active cell using VBA? - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Pasting to a location specified within an active cell using VBA?

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,116
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Not sure if I've understood correctly, but try this
    Code:
        Sheets("Sheet2").Range(ActiveCell.Value).Value = Sheets("Test").Range("M3")
    Changing sheets names in red to suit
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  2. #12
    New Member
    Join Date
    Jul 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Quote Originally Posted by Fluff View Post
    Not sure if I've understood correctly, but try this
    Code:
        Sheets("Sheet2").Range(ActiveCell.Value).Value = Sheets("Test").Range("M3")
    Changing sheets names in red to suit
    I tried this macro out:

    Sub CopyM3toTableOneActiveCell()
    Sheets("Amending Tasks").Range(ActiveCell.Value).Value = Sheets("Task manager").Range("M3")
    End Sub

    It seemed to run without error code but didn't do anything (that I can see).

    Kind regards,

    Doug.

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,116
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Did the activecell have a value like G4?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  4. #14
    New Member
    Join Date
    Jul 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Quote Originally Posted by Fluff View Post
    Did the activecell have a value like G4?
    Yes.

    For visual reference, the cells I want to select as active are in purple i.e., E5:E7: so G4 or G5 or G6 etc.

    Worksheet 2 - Table 2

    PRIORITY (B4) JOB NUMBER (C4) JOB NAME(D4) Details Location (E4)
    1 1 JOB A G4
    2 2 JOB B G5
    3 etc... 3 JOB C G6


    So E5, E6 and E7 in table 2 need to provide the macro with the cell address for Table 1 column G4, G5, G6 (JOB DETAILS) in blue.


    JOB NUMBER (A2) TASKS STATUS (B2) TASK TYPE (C3) DATE (D3) TIME (E3) JOB NAME (F3) JOB DETAILS (G3) DETAILS LOCATION (H3) PRIORITY (I3) REQUESTED DATE (J3)
    1 1 EXCEL VBA 07/12/2017 09:00 JOB A SLKJDLFKJDLJKFLDJFKJDLFJKDFJKLDJFKLJDKLFJKLDJ G4 URGENT 07/12/2017
    2 1 ADMIN 07/12/2017 09:00 JOB B ADJGDAJKGLSAJDLFGJDAFLGJFDLJGALJFLDJSKLDAJFGK G5 CURRENT INITIATIVE 15/01/2018
    3 1 OTHER 07/12/2017 09:00 JOB C AJGKLFJGJKHDAJDFKLASDJFGKJDALFJKLSDAJFKLDJKF G6 BY TOMORROW 08/12/2017

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,116
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    What is the sheet name containing table 2?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  6. #16
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,872
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Normally when writing scripts we do not like to refer to cells as the "activecell"

    And we normally do not like using "Select"

    If you wanted to put "George" into Range("J45") of sheet named "Bob" we would write the script like this:

    Sheets("Bob").Range("J45").value="George"

    Or something like this if you also wanted the formatting also:

    Sheets("Sally").Range("B1").Copy Sheets("People").Range("J3")

    And when posting images it's also best to show what columns we are dealing with
    We cannot always assume the column on the far left of the image is column "A"
    And do not say Column("Sally") that then requires us to figure out what column that is.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #17
    New Member
    Join Date
    Jul 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Quote Originally Posted by Fluff View Post
    What is the sheet name containing table 2?
    Hi Fluff,

    The sheet name containing table 2 is "Amending Tasks"

    Kind regards,

    Doug.

    Worksheet 1 = Task manager (Table 1)

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,116
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    In that case try
    Code:
    Sub CopyM3toTableOneActiveCell()
    Sheets("Task manager").Range(ActiveCell.Value).Value = Sheets("Amending tasks").Range("M3")
    End Sub
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  9. #19
    New Member
    Join Date
    Jul 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    Quote Originally Posted by Fluff View Post
    In that case try
    Code:
    Sub CopyM3toTableOneActiveCell()
    Sheets("Task manager").Range(ActiveCell.Value).Value = Sheets("Amending tasks").Range("M3")
    End Sub
    Hi Fluff,

    You've cracked it, awesome Thanks for your help!
    This will be very useful in future. How did you learn VBA?

    Kind regards,

    Doug.

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,116
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Pasting to a location specified within an active cell using VBA?

    ††
    Glad to help & thanks for the feedback.

    I've learnt most of what I know from this site. Either hunting for answers that I could understand (to some extent) & modify for my needs, or by studying solutions that others have offered, on threads that I've been involved with.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com