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

[dougmarkham]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]JOB NUMBER (A2)[/TD]
[TD]TASKS STATUS (B2)[/TD]
[TD]TASK TYPE (C3)[/TD]
[TD]DATE (D3)[/TD]
[TD]TIME (E3)[/TD]
[TD]JOB NAME (F3)[/TD]
[TD]JOB DETAILS (G3)[/TD]
[TD]DETAILS LOCATION (H3)[/TD]
[TD]PRIORITY (I3)[/TD]
[TD]REQUESTED DATE (J3)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]EXCEL VBA[/TD]
[TD]07/12/2017[/TD]
[TD]09:00[/TD]
[TD]JOB A[/TD]
[TD]SLKJDLFKJDLJKFLDJFKJDLFJKDFJKLDJFKLJDKLFJKLDJ[/TD]
[TD]G4[/TD]
[TD]URGENT[/TD]
[TD]07/12/2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]ADMIN[/TD]
[TD]07/12/2017[/TD]
[TD]09:00[/TD]
[TD]JOB B[/TD]
[TD]ADJGDAJKGLSAJDLFGJDAFLGJFDLJGALJFLDJSKLDAJFGK[/TD]
[TD]G5[/TD]
[TD]CURRENT INITIATIVE[/TD]
[TD]15/01/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]OTHER[/TD]
[TD]07/12/2017[/TD]
[TD]09:00[/TD]
[TD]JOB C[/TD]
[TD]AJGKLFJGJKHDAJDFKLASDJFGKJDALFJKLSDAJFKLDJKF[/TD]
[TD]G6[/TD]
[TD]BY TOMORROW[/TD]
[TD]08/12/2017[/TD]
[/TR]
</tbody>[/TABLE]

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

Worksheet 2 - Table 2

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]PRIORITY (B4)[/TD]
[TD]JOB NUMBER (C4)[/TD]
[TD]JOB NAME(D4)[/TD]
[TD]Details Location (E4)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]JOB A[/TD]
[TD]G4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]JOB B[/TD]
[TD]G5[/TD]
[/TR]
[TR]
[TD]3 etc...[/TD]
[TD]3 [/TD]
[TD]JOB C[/TD]
[TD]G6[/TD]
[/TR]
</tbody>[/TABLE]








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

 

[My Aswer Is This]

Try this:

ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value

 

[dougmarkham]

Try this:

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.

 

[My Aswer Is This]

Are you saying the script I provided worked or you came up with your own answer which does not work?

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.

 

[My Aswer Is This]

Just use the script I provided. You do not need to activate the sheets.

 

[dougmarkham]

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")?

 

[My Aswer Is This]

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"

Sub My_Script()
ActiveCell.Value = Sheets("Amending Tasks").Range("M3").Value
End Sub

 

[dougmarkham]

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"

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.

 

[My Aswer Is This]

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.

 

[dougmarkham]

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