find the last used column and paste the rows alongside the last column.

[mvneema]

I am trying to format my worksheet. I am trying write a macro that finds the last used column (in this case it is Column V) and select the rows range for each company(Column E to Column J) and paste to the Column W( next to the last used column). And finally delete the empty rows. What I need is a row for each company. I have attached the sheet. It would be nice if someone could help me on this.

[mvneema]

I tried with this code right now.

Please Login or Register  to view this content.

Now I get an error "runtime 1004 : Application defined or object defined error". I know that someone would know how to solve this issue. Thanks in advance.

[JOHN H. DAVIS]

I'm not entirely sure I understand, so try on a copy first.

Please Login or Register  to view this content.

[mvneema]

Your code cuts the Column values E to J and Paste it to the Column W. But my requirement was I need rows to be copied alongside the columns starting from W. Say I need row 4 to cut and paste in to Range (W3:AB3) and row 5 to be pasted in range (AC3 : AH3) and row 6 to (AI3:AK3) like wise in the second company as well. just like the sheet 2 in the workbook attached.

[mvneema]

Hi john, I tried writing the code to paste the rows alongside except I dont know how to paste those values starting from Column W. the snippet does that

Please Login or Register  to view this content.

here FoundDate is Set FoundDate = Worksheets("Sheet1").Columns(ColumnLetter) But gets an error. I am not sure if I am doing it write. it would great if you could help me.

[mvneema]

I wrote this code that give no error but it did not meet my purpose.

Please Login or Register  to view this content.

Please find the workbook attached I get the output in sheet but it is wrong i need it like in Sheet 2. i.e. I need to paste range Q:AE to be pasted from W onwards since W is the next cell after the usedRange. Please help

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[mvneema]

thanks for the response. you are close to the solution. Right now in row 3 it loads the values from Column P till the end but it has to be pasted from Column W since that was last column used in the input sheet. I need a empty cells between Column Q to Column V. Do you understand what I mean?

[AB33]

Try the attached.

[mvneema]

hi ab33, Thank you for the response. I just saw the sheet2 the row3 has the Proposal 2 values from row 4 to row 6 pasted in Column Q to Column V. But I need the values to be pasted from Column W onwards for row three. That is, I am trying to find the last used column in Sheet 1 and then paste the values from that last column like you did. Its just that now its not pasted in the right column. I appreciate your help.

[AB33]

Try the attached.

[mvneema]

thank you ab33. your code works exactly what i wanted. Thank you all for the help

[AB33]

mvneema,
You are welcome!
Please do not forget to mark the thread as solved.