Copy selected items to a contiguous list ion another worksheet

[Michael Jones]

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

 

[Sektor]

Anything to show?

 

[My Aswer Is This]

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.

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]

Or if you need to copy the cells which copies formatting also thy this:

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]

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