Getting mouse selected cell as input to start operations on data of that cell row

[reach78]

Hi all,

This is what I am trying to do:

1) Launch macro
2) macro will prompt me to select a cell
3) I will use my mouse to click on the cell within a table
4) macro will then use the row value of that cell to grab data off the whole row
5) using the data I will populate another sheet

Problem is step 3. How do I get an input from cell selection?

[Leith Ross]

Hello reach78,

Clicking a cell makes it the ActiveCell. You can use the Row property to obtain the row of cell.

Please Login or Register  to view this content.

[romperstomper]

Use Application.inputbox with the Type argument set to 8 to return a Range object.

[reach78]

Hi All,

Thanks for the input so far. I managed to get the thing going but am having trouble trying to get the row value from the range object once I selected the row I wanna use. Here's the code I have generated

Please Login or Register  to view this content.

How do I get the row value so I can select a range within that selected row? I'm trying to copy from column 2 to column 7 within that row

[romperstomper]

It's just row.row (not a good name for a variable!) instead of Range(Row).Row

[reach78]

The code below now works:

Please Login or Register  to view this content.

However when I replace:

Please Login or Register  to view this content.

To call a specific cell and paste it to a specific location in another sheet. It breaks down. The code I replaced it with is

Please Login or Register  to view this content.

The debugger hangs at the Select.PasteSpecial... line. Code somewhere above it wrong I guess?

[romperstomper]

You're still using Set row =

[reach78]

Hi romperstomper,

Nope, I've changed it before to a new variable name following your suggestion

I tried the macro with a paste on the same worksheet it works fine. But if I try to paste it to a specific cell in another worksheet, then it all goes wrong. I think it has to do with how I am calling out the other sheet. I'm still new to VBA so am shaky when it comes to semantics/syntax/knowing the vba objects etc

[reach78]

Attached screenshot showing the error line--> error.jpg

[reach78]

Nevermind, I feel so stupid...

I was trying to paste to a 'merged' cell. I unmerged it. Ran the macro....and it works...Doh!

[Fotis1991]

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.