Pasting to a location specified within an active cell using VBA?
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

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

  1. #1
    Board Regular
    Join Date
    Jul 2016
    Posts
    54
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Hi Guys,

    I am attempting to finish off a project for the team and wondering if VBA can achieve an unusual task.

    Goal: to copy the contents of cell (M3) in worksheet 2 into clipboard; thereafter, to paste the clipboard contents into a location in worksheet 1 i.e., into a cell which is specified by an active cell in worksheet 2 (selected prior to running the macro). In other words, I want the macro to refer to a location stored within an active cell but to paste clipboard into that location in worksheet 1.

    Situation
    I have two worksheets:
    Worksheet 1 - Task manager (containing Table 1)
    Worksheet 2 - Amending Tasks (containing Table 2)


    Worksheet 1, Table 1:


    • The Task manager (Table 1) contains the full list of tasks with four named ranges corresponding to JobNumber, JobName, Detail and DetailsLocation.


    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













    • whereas, the Amending Tasks worksheet (Table 2) contains only active tasks.


    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








    In Worksheet 1, a user inputs tasks as they are assigned. Over time, this generates a big list that grows and becomes a hassle to search through---i.e., because the cells in the job details column become huge.

    So, I created a second worksheet (2) containing a table which uses INDEX MATCH to extract only active tasks (active task = 1, completed task = 0 in column B of Table 1 below). Also in worksheet 2---next to the Table 2---I have a big cell (M3) which the user can write amendments to the Job Details for a particular task. I would like the user to be able to click a button to paste the amended job details from cell M3 back into the relevant cell in table 1.

    E.g., the process for the user would go something like:
    1) Work on a specific task e.g., Job A (in Table 2)
    2) Enter an amendment that task's Job details in cell M3
    3) Click on cell E5 in Table 2 to make it the active cell
    4) Click the button to activate the macro which pastes M3 to cell G4 of Table 1.

    My idea is for the user to select a cell in column E of Table 2 corresponding to their specific task (i.e., so the active cell then contains a cell address which the macro will use to paste to that cells address in Table 1) and then run the macro to paste M3 contents into Worksheet 1.

    So far I've written the following VBA:
    ThisWorkbook.Sheets("Amending Tasks").Activate
    ActiveSheet.Range("M3").Select
    Selection.Copy

    My question is: if it is possible, how would one instruct excel via VBA code to paste the contents of M3 to a cell address in Table 1 (worksheet 1) that is defined by an active cell in Table 2 (worksheet 2) e.g., cell E5 in Table 2 contains the cell address G4??

    Kind regards,

    Doug
    Last edited by dougmarkham; Dec 7th, 2017 at 05:53 AM.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

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

    Try this:
    Code:
    ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
    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知 not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Jul 2016
    Posts
    54
    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 My Aswer Is This View Post
    Try this:
    Code:
    ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
    Thanks for replying!

    I used this code:

    Sub CopyM3SelectActiveCellPasteIntoTable1()



    ThisWorkbook.Sheets("Task Priority").Activate
    ActiveSheet.Range("M3").Select
    Selection.Copy
    ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
    End Sub

    Excel's response was: Run-time error '9': Subscript out of range (highlighted row shown in yellow).
    How might this be resolved?

    Kind regards,

    Doug.

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

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

    Are you saying the script I provided worked or you came up with your own answer which does not work?
    Quote Originally Posted by dougmarkham View Post
    Thanks for replying!

    I used this code:

    Sub CopyM3SelectActiveCellPasteIntoTable1()



    ThisWorkbook.Sheets("Task Priority").Activate
    ActiveSheet.Range("M3").Select
    Selection.Copy
    ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
    End Sub

    Excel's response was: Run-time error '9': Subscript out of range (highlighted row shown in yellow).
    How might this be resolved?

    Kind regards,

    Doug.
    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知 not perfect yet. "Memories are forever"

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

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

    Just use the script I provided. You do not need to activate the sheets.
    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知 not perfect yet. "Memories are forever"

  6. #6
    Board Regular
    Join Date
    Jul 2016
    Posts
    54
    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 My Aswer Is This View Post
    Just use the script I provided. You do not need to activate the sheets.
    Apologies, I've removed my code and tried it again.
    The error message returned was identical.

    Would I not need extra lines of code to paste into Sheet("Task manager")?
    Last edited by dougmarkham; Dec 7th, 2017 at 06:57 AM.

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

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

    If all we are doing is entering values we do not need to copy and paste.

    For example just use this little script on any sheet and see what happens:

    Activecell.value= Range("A1").value

    If you use just this script and your getting a error.
    It's because you do not have a sheet named: "Amending Tasks"
    Code:
    Sub My_Script()
    ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
    End Sub
    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知 not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Jul 2016
    Posts
    54
    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 My Aswer Is This View Post
    If all we are doing is entering values we do not need to copy and paste.

    For example just use this little script on any sheet and see what happens:

    Activecell.value= Range("A1").value

    If you use just this script and your getting a error.
    It's because you do not have a sheet named: "Amending Tasks"
    Code:
    Sub My_Script()
    ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
    End Sub
    Yes, I've tried Activecell.value= Range("A1").value out in another sheet. It is copying the data inside A1 and pasting it into the active cell.

    What I'm trying to do is different.

    In Table 1, you'll see for Job A, there is a details entry (cell G4) full of gibberish for example purposes. Using index matching, this data is extracted to L3 of Worksheet 2 (Amending Tasks), which is then pasted into M3 so that the user can amend the details. The user does some part of the task and wishes then to amend the job details so next time the task is worked on, they can see where they are up to. Thus, in order to update "Table 1 cell G4" with the amended job details, I wish to find a macro to copy M3 and paste it into e.g., in the example of JOB A, cell G4 of Table 1. As the location "Table 1 cell G4" is shown in Table 2 ("cell E5"), I want to be able to select "Table 2 cell E5" (which contains the text "G4") to make it the active cell. This would then tell the macro to paste M3 data into Table 1, cell G4.

    I know that's a tad complicated. I think that my first post explains it but I purposely left out as much detail as possible to cut down read time.
    Last edited by dougmarkham; Dec 7th, 2017 at 07:44 AM.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    11,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    3 Thread(s)

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

    Well this is more complicated then I first thought.
    And there are always others on this forum who will see this post. So I'm sure one of them will be able to help you.
    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知 not perfect yet. "Memories are forever"

  10. #10
    Board Regular
    Join Date
    Jul 2016
    Posts
    54
    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 My Aswer Is This View Post
    Well this is more complicated then I first thought.
    And there are always others on this forum who will see this post. So I'm sure one of them will be able to help you.
    No problem, I thought it wasn't going to be straight forward

    Thanks anyway

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