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

 

[dougmarkham]

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.

Looks like I will follow your example because every week my boss or colleagues are asking for either VBA or formula solutions. Kinda fun!