Help adapting macro for my C&P needs

[capek]

New to the forum, and new to using macros in excel. I have a pretty basic C&P need, but am not sure how to set the range so that it's "dynamic," as opposed to it being stuck at the specific cells used when I recorded the macro. Basically, imagine three columns in an excel sheet. From outside excel I need to copy three rows of data, but want to transpose those three rows into three columns of a single row in excel. I recorded the following macro:

Please Login or Register  to view this content.

This worked great for the first row used when recording the macro, but when I tried it for the subsequent entry in the following row, obviously it pasted the new data in the previous row. So, what do I need to do to adjust the macro so that the function takes place relative to the cell I have selected. Basically, I have 100 rows of data to paste in per excel file, and I need to go row by row transposing three rows of data into three columns in a single row, row after row.

Thanks for the help, I really appreciate it.

[alansidman]

@capek
using code tags instead of quote tags make it easier to read the code. I have changed them for you.

[capek]

using code tags instead of quote tags make it easier to read the code. I have changed them for you.

Ah! That does make sense. Thanks for fixing it, I'll be mindful of this in the future.

[mjr veverka]

... for the subsequent entry
... the function takes place relative to the cell I have selected
... I have 100 rows of data
... row by row transposing three rows of data into three columns in a single row, row after row

Try

Please Login or Register  to view this content.

[capek]

Thanks for the response! And sorry there was lack of clarity in my OP, I was worried that might be the case. I tried out your suggestion, and while it captures the basic idea, it seems to clear all content from the sheet, and each subsequent paste clears out what was previously pasted. Let me attach an example doc which will hopefully clarify what I have in mind.

If you look at that file, what I'm needing to do is copy three rows of data from outside excel, paste and match destination formatting into the correct worksheet, and then transpose the three pasted rows into the three empty columns in Row2, and then delete the originally pasted rows. Then I need to copy three new rows of data from outside excel, and perform the same process so that they end up in the three empty columns of Row3. And so on and so forth.

What I am currently doing is copying the three rows of data from outside excel and using the customized ribbon buttons in the attached workbook to more manually achieve the desired result. I select C3 to begin the process of getting the desired data into Row2, then select C4 to being the process of getting the next set of desired data into Row3, etc etc. If I could do this all in one click though via the correct macro, it would save me so much time!

Does this better clarify what I have in mind? Thanks again for the help!