Select Multiple cells, Combine in a sequence and paste them in next empty Row in another Sheet

X

xs4amit

New Member
Joined
May 21, 2018
Messages
34
Hi,

I am trying to copy multiple (Non Continuous) cells from sheet1 and wants to arrange them in an order (in a single Row) and paste them in next available Row.
For example:[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD]XYZ[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LEF[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

now i want to paste them in next sheet like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD]XYZ[/TD]
[TD]LEF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And next time i need that the values should go to the next available empty Row automatically. Any idea how to do that??
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Does the consolidation run by rows or columns? In other words, if you initially had:

Rich (BB code): 
	A	B	C	D	E
1					
2		ABC			XYZ
3		DEF			LEF
4					
5

would it go same as you said, or instead like this:

Rich (BB code): 
	A	B	C	D	E
1	ABC	XYZ	DEF	LEF	
2


Also, what do you mean exactly by "next time"? How are the cells populated? What triggers the new arrangement?
 
Last edited:
Upvote 0
iliace said:
Does the consolidation run by rows or columns? In other words, if you initially had:

Rich (BB code): 
    A    B    C    D    E
1                    
2        ABC            XYZ
3        DEF            LEF
4                    
5

would it go same as you said, or instead like this:

Rich (BB code): 
    A    B    C    D    E
1    ABC    XYZ    DEF    LEF    
2


Also, what do you mean exactly by "next time"? How are the cells populated? What triggers the new arrangement?
Click to expand...

Hi Iliace,

thanks for showing interest. The consolidation will go the same as i said. Next time new values will populate exactly in the same cells.
 
Upvote 0
Can you try this and reply with feedback? Run this with your sheet activated, as in the example you showed above.

Code: 
Public Sub AutoArrange()
  Dim rngArea As Excel.Range
  Dim rngNext As Excel.Range
  
  Dim rng As Excel.Range
  
  Dim i As Long, j As Long
  
  Set rngArea = ActiveSheet.UsedRange
  
  Set rngNext = ActiveSheet.Range("A1")
  
  If Len(rngNext.Value) > 0 Then
    With rngNext.EntireRow
      Set rngNext = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
    End With
    With rngArea
      Set rngArea = .Offset(1, 0).Resize(rowsize:=.Rows.Count - 1)
    End With
  End If
  
  For j = 1 To rngArea.Columns.Count
    For i = 1 To rngArea.Rows.Count
      Set rng = rngArea.Cells(i, j)
      If Len(rng.Value) > 0 Then
        rng.Copy rngNext
        rng.Clear
        Set rngNext = rngNext.Offset(0, 1)
      End If
    Next i
  Next j

End Sub
 
Last edited:
Upvote 0
iliace said:
Can you try this and reply with feedback? Run this with your sheet activated, as in the example you showed above.

Code: 
Public Sub AutoArrange()
  Dim rngArea As Excel.Range
  Dim rngNext As Excel.Range
  
  Dim rng As Excel.Range
  
  Dim i As Long, j As Long
  
  Set rngArea = ActiveSheet.UsedRange
  
  Set rngNext = ActiveSheet.Range("A1")
  
  If Len(rngNext.Value) > 0 Then
    With rngNext.EntireRow
      Set rngNext = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
    End With
    With rngArea
      Set rngArea = .Offset(1, 0).Resize(rowsize:=.Rows.Count - 1)
    End With
  End If
  
  For j = 1 To rngArea.Columns.Count
    For i = 1 To rngArea.Rows.Count
      Set rng = rngArea.Cells(i, j)
      If Len(rng.Value) > 0 Then
        rng.Copy rngNext
        rng.Clear
        Set rngNext = rngNext.Offset(0, 1)
      End If
    Next i
  Next j

End Sub
Click to expand...

Hi Iliace,

Your code is fantastic. But it is not what i was in need of (but i learned how to arrange cells in order). I will elaborate my needs a little more:

1. First thing, there are few more cells which need to be arranged and rest of the cells are not empty, they also have data. So the table which need to be arranged will be like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Amit[/TD]
[TD]Date[/TD]
[TD]23/May/2018[/TD]
[TD]Meeting Status[/TD]
[TD]Scheduled[/TD]
[/TR]
[TR]
[TD]Emp ID[/TD]
[TD]A1620[/TD]
[TD]Time[/TD]
[TD]12:00:00[/TD]
[TD]Meeting Time[/TD]
[TD]14:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Meeting ID[/TD]
[TD]Meeting Password[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]567493[/TD]
[TD]*12345*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


2. Once all the details are filled, this should go to next sheet "Sheet2" and search for next available empty Row and arrange it like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Emp ID[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Meeting Status[/TD]
[TD]Meeting Time[/TD]
[TD]Meeting ID[/TD]
[TD]Meeting Password[/TD]
[/TR]
[TR]
[TD]iliace[/TD]
[TD]A1593[/TD]
[TD]22/May/2018[/TD]
[TD]11:00:00[/TD]
[TD]Completed[/TD]
[TD]12:00:00[/TD]
[TD]543728[/TD]
[TD]*34567*[/TD]
[/TR]
[TR]
[TD]Amit[/TD]
[TD]A1620[/TD]
[TD]23/May/2018[/TD]
[TD]12:00:00[/TD]
[TD]Scheduled[/TD]
[TD]14:00:00[/TD]
[TD]567493[/TD]
[TD]*12345*[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps to understand it better. Thanks a lot for your valuable time and help.
 
Upvote 0
Hi Amit - thank you for clarifying.

Let me make sure I am understanding correctly.

Are the headers always consistent? In other words, Name, Emp ID, Date, etc - those will always appear on the source sheet? Matching what it says on Sheet2?

Now, on the source sheet, you will have a header, and then the corresponding piece of data, in an adjacent cell, either below or to the right?

In that case, what do you do when there is ambiguity; for example:

[table="width: 500"]
[tr]
[td][/td]
[td]Time[/td]
[td]12:00:00[/td]
[/tr]
[tr]
[td]Name[/td]
[td]Amit[/td]
[td][/td]
[/tr]
[/table]

How do you propose, in this instance, to determine whether Time is "12:00:00" or "Amit"?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Copy and paste range of data to next available row on different sheet
2 3
Replies
22
Views
1K
Trae1170
T
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
508
Mplz
M
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
631
bh24524
B
M
  • Solved
VBA to copy selected cell value to next empty cell in a range
Replies
3
Views
491
MeaclH
M
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
565
B-radK
B

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top