Macro with relative reference does not copy a fixed location of cell data

[isapien]

I've tried for days to create a macro that inputs data copied from a fixed series of cells, into a row beginning at the cell I've selected, when the macro is executed (the cell data in the fixed location changes based on my input elsewhere in the spreadsheet). When it has finished, I would like for it to move to the next line automatically. The macro I create starts at the cell I've selected, but because it's relative, it will go to the next line of data to copy at the fixed location. Hard to explain so I've attached a spreadsheet with an example of what I would like to accomplish. Help!

Thank you

[rollis13]

Copy this event macro into the sheet's module. I'm using event BeforeRightClick because it will give you more control than BeforeDoubleClick or SelectionChange.
If it doesn't do exacty what you are after you can use it as a starting point and adapt it to your needs.

Please Login or Register  to view this content.

[isapien]

Thank you for your quick reply. I'm not experienced with macros and I'm sure I'm making a dumb entry error. When I enter the macro and attempt to execute it on the sample spreadsheet, it generates an error. See pic attached.
Picture2.png

[rollis13]

What is that "Sub Macro2()" doing there without its "End Sub" closure? Each macro has its own name and closes with "End Sub" and cannot be one inside the other.
Also, you don't need to execute the macro, it triggers automatically when you double-click a cell in column A of the sheet in whose module you pasted it.

[isapien]

I'm sure your solution works rollis13, but despite researching the Option Explicit command and trying many ways to enter the code, I cannot make it work.
I just don't know enough about macro writing and programming. I understand the "Sub Macro2()" and "End Sub" closure comment, but despite removing those lines,
nothing I try works. Thank you for trying to provide a solution. If you can think of another way that may be more simplistic, please let me know.
All I'm trying to do is populate a log I keep of my stock transactions. Rather than entering that data manually in each row under various column headings,
which takes a lot of time, I'd like to just place the curser in column A, on whatever row I wish to begin the log entry and push a macro button. The macro
executed would copy and paste the data from a fixed location to designated columns in the same row, starting with the cell I place the curser on. Then I would
go to the next row and repeat the process. The fixed data copied would change based on a manual option that changes it and would not be a part of the macro.
I wish I could explain it better. Thanks again for trying to help!

[rollis13]

Attached you will find your file with this updated macro. Usage: choose a cell in column A and right-click, that's all.

Please Login or Register  to view this content.

[isapien]

Thank you! It works. Now I'd like to transfer it to my log spreadsheet and modify it to copy the data and place it in the selected row. I found it on the Relative Reference Macro Question.xlsm when I opened the file by going to the Developer tab and selecting Visual Basic. Where would I find it or where would I insert the code on my actual log spreadsheet in order to modify it?

[rollis13]

As said in post #2 and #4 the macro goes in the sheet's module so go to the VBE pane with keys Alt+F11, then on the left open your sheet in which this macro has to work and paste it in the right pane. Even faster, from the Excel pane, right-click the Tab of your sheet and choose "View Code" then paste the macro.

[isapien]

Thank you. I was able to modify the solution to my needs and the code worked well. I've learned a lot and I appreciate your help very much. One final question: How do I modify the code to make it copy only the text in the cells being copied? The current code copies the formulas to the individual destination columns/rows and writes over the column formatting as well -something I don't want to do. I need the formatting to remain intact in the destination columns/cells.
Thanks again for your help.

[rollis13]

Not sure if I correctly understood your request since there were no formulas in your example file. So I'll try to guess a solution:

Please Login or Register  to view this content.

[isapien]

Perfect. Works great! I'll save so much time and effort entering data. Thanks for being patient with my questions. I'll close this thread as a sucess, soon.

[rollis13]

Glad having been of some help.