Copy selected items to a contiguous list ion another worksheet

Michael Jones

New Member
Joined
Jul 15, 2014
Messages
19
I am attenmpting to copy selected items in one worksheet to another, but require the copied list to be contiguous, no spaces. I have searched other posts and attempted various formulae, but without success. Does anyopne out there have a solution for me?

Thank you all in advance for any suggestions:confused:
 
Last edited:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try this script:
Assuming selected items means selected Range of cells.


So on the sheet where you want to run this script from select a range
Then this script will copy all cells with values into Sheet(2) column A
Modify script if needed to your needs or give more specific details.

Code:
Sub Test()
'Modified 5/10/2018 4:40 AM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim i As Long
i = 1
    For Each c In Selection
        If c.Value <> "" Then Sheets(2).Cells(i, 1).Value = c.Value: i = i + 1
        Next
Application.ScreenUpdating = True
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Or if you need to copy the cells which copies formatting also thy this:
Code:
Sub Test()
'Modified 5/10/2018 4:47 AM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim i As Long
i = 1
    For Each c In Selection
        If c.Value <> "" Then c.Copy Sheets(2).Cells(i, 1): i = i + 1
    Next
Application.ScreenUpdating = True
End Sub
 

Michael Jones

New Member
Joined
Jul 15, 2014
Messages
19
Re: Copy selected items to a contiguous list in another worksheet

Many thanks for your prompt response. This appears to be just what I am looking for.


I will try this out later and let you know how I get on


Michael
 

Watch MrExcel Video

Forum statistics

Threads
1,100,195
Messages
5,473,080
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top