Beginner - Need to copy data between columns

[DaChocapic]

Im trying to select a range, and then copy the formulas inside it to the next row on the right

I wrote this with the help of the macro recorder and some code I found online but it doesn't work.

The last line makes it crash.

Can somebody help me with this ?

Please Login or Register  to view this content.

[Arkadi]

Your code goes all the way to the last column to the right... selects a few rows copies and then tries to paste one column to the right... that is past the right-most column.

I don't know what range you want to copy but the code below would copy A6 to A7 and paste it in B6 (and below).

Please use code tags when posting code.

Please Login or Register  to view this content.

[DaChocapic]

Thank you for your reply.
What I would like to do is not to copy A6:A7 in B6 but to copy the last column that is not empty, in the next empty column. How would I do that?

Thanks for the tip about the code tags as well.

[Arkadi]

What row would be used to decide the last column with data?

[Arkadi]

This code would find the last column with data based on row 1 and copy the whole column to the next one to the right.

Please Login or Register  to view this content.

[alansidman]

@DaChocapic
Welcome to the forum. It appears that you did not take the time to read our few forum rules when you joined. Please note the following issues with your post and take time to read and understand our forum rules.



Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)


Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[DaChocapic]

@Arkadi

Thank you, this seems to work.

Can you help me understand it ?

Please Login or Register  to view this content.

Did I get this right?

@alansidman got it, thank you for your help, I'll make sure to be more carefull for my next post.

[Arkadi]

DaChocapic:

These 2 lines just define the variables we are going to use. lastCol is a number (long integer), and ws is a worksheet object

Please Login or Register  to view this content.

Then we use "Set" for the worksheet object, which means we make ws in the code represent the same as "Worksheets("Sheet1")". It makes it easier to code using "ws" instead of typing it out each time. Also easier to edit if you need to change the sheet name, since you only need to change it in one place:

Please Login or Register  to view this content.

the lastCol variable is used to reference the last column with data... the formula starts on the right, and finds the last one with data by moving to the left. Same idea can be used for the last row in a column:

Please Login or Register  to view this content.

Then we copy the column number assigned to "lastCol", and when you use the copy function you can specify the destination next to it.

Please Login or Register  to view this content.

Columns(lastCol).Offset(,1) will move one column right from Columns(lastCol), notice the blank before the comma... which means we don't offset row, only column.

You did great using the code tags there, thanks. Please also note that the description should be more indicative of what you actually need, something more like "Need to copy data between columns"... or something like that.

[DaChocapic]

Thank you very much for your help

[Arkadi]

My pleasure

[FDibbins]

DaChocapic and Arkadi please take note that when a senior member requests that some action be taken (thread title change in this case), that complying with that request is not optional, and the tread should receive no further posts until the required action is carried out./

[Arkadi]

@ FDibbins... I noticed his code tags and had forgotten myself... I did edit my post to request for a change in description as well, I should have deleted my post I guess. I apologize, the intent was not to ignore the request by alanisdman and have received my infraction accordingly.

Again, I apologize to you both.

[Arkadi]

Ooops, double posted.

[DaChocapic]

Sorry, I did want to do it and then got carried away by my problem. Will do now