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

D

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:
Sort by date Sort by votes
Not sure if I've understood correctly, but try this
Code: 
    Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range(ActiveCell.Value).Value = Sheets("[COLOR=#ff0000]Test[/COLOR]").Range("M3")
Changing sheets names in red to suit
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Fluff said:
Not sure if I've understood correctly, but try this
Code: 
    Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range(ActiveCell.Value).Value = Sheets("[COLOR=#ff0000]Test[/COLOR]").Range("M3")
Changing sheets names in red to suit
Click to expand...

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.
 
Upvote 0
Did the activecell have a value like G4?
 
Upvote 0
Fluff said:
Did the activecell have a value like G4?
Click to expand...

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

[TABLE="class: cms_table_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]


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.


[TABLE="class: cms_table_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]
 
Upvote 0
What is the sheet name containing table 2?
 
Upvote 0
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.
 
Upvote 0
In that case try
Code: 
Sub CopyM3toTableOneActiveCell()
Sheets("Task manager").Range(ActiveCell.Value).Value = Sheets("Amending tasks").Range("M3")
End Sub
 
Upvote 0
Fluff said:
In that case try
Code: 
Sub CopyM3toTableOneActiveCell()
Sheets("Task manager").Range(ActiveCell.Value).Value = Sheets("Amending tasks").Range("M3")
End Sub
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
60
user29383902
U
R
  • Question Question
Offset result in separate cell
Replies
1
Views
163
KOKOSEK
KOKOSEK
H
  • Question Question
Positioning active cell when adding table rows.
Replies
3
Views
183
myall_blues
myall_blues
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
624
mlan1604
M
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
189
sijpie
sijpie

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top