Copy two non-contiguous columns from one sheet to a single column in another sheet using VBA

[JCK101]

Let's say that I have a sheet("Members") with Columns A through Z

The column and header names are:
C- MemberID
D- First Name
E- MiddleName
F- Last Name
H- TelNo

I want to copy from sheet(“Members”) to sheet(“LkupList”)
Column(“C”) to column(“A”) as MemberID
Column(“D”)plus a space plus column(“F”) to column(“B”) as FullName
Colum(“H”) to column(“C”) as TelNo

And then sort the data on sheet(“LkupList”) by column (“A”) or column(“B”) or column(“C”)

I read a lot of posts in the last couple of months and I know how to copy a range of consecutive columns and sort them, but cannot figure how to do non-contiguous columns and combine two of them.

Any help is much appreciated.

 

[cml19722000]

Let's say that I have a sheet("Members") with Columns A through Z

The column and header names are:
C- MemberID
D- First Name
E- MiddleName
F- Last Name
H- TelNo

I want to copy from sheet(“Members”) to sheet(“LkupList”)
Column(“C”) to column(“A”) as MemberID
Column(“D”)plus a space plus column(“F”) to column(“B”) as FullName
Colum(“H”) to column(“C”) as TelNo

And then sort the data on sheet(“LkupList”) by column (“A”) or column(“B”) or column(“C”)

I read a lot of posts in the last couple of months and I know how to copy a range of consecutive columns and sort them, but cannot figure how to do non-contiguous columns and combine two of them.

Any help is much appreciated.

Example: https://www.box.com/s/k8pitornma1yove8i9yc

Hope this help.

 

[JLGWhiz]

I will let you do the sorting.

Code:
Sub switcheroo()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets("Members")
Set sh2 = Sheets("LkupList")
sh1.Range("C1", sh1.Cells(Rows.Count, 3).End(xlUp)).Copy sh2.Range("A1")
sh2.Range("B1") = "FullName"
lr = sh1.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lr
If sh1.Cells(i, 4) <> "" Then
sh2.Cells(Rows.Count, 2).End(xlUp)(2) = sh1.Cells(i, 4).Value & " " & sh1.Cells(i, 6).Value
End If
Next
sh1.Range("H1", sh1.Cells(Rows.Count, 8).End(xlUp)).Copy sh2.Range("C1")
End Sub
Code:

 

[JCK101]

That was quick. Thank you very much for your help. It will take me some time to implement it, I like to study it in detail so I can use it in future projects too.

 

[JCK101]

Thank you very much for your help, I want to try it together with cml19722000's example, I will come back and post if I have further questions.