Copy Multiple non adjacent columns from one Worksheet to another

[Blubber]

Hi guys,
I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from
worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I
know how to select ajacent columns but how do we deal with non ajacent ones?

Would appreciate some help codding. Thanks

 

[Daniel.C]

Hi.
Range("A:H,K:K,M:M,N:N,S:S,U:U")
Note that you may have trouble with pasting not adjacent columns.
Regards.
Daniel

 

[Blubber]

Thanks Daniel,

I have one more complication. I need to select ranges A15 to Last cell
Column H that has entry, K15 to Last cell in Column K, M15 to Last cell in
column M.........

Possible to do a one line range reference to this multiple ranges?

I tried:
Dim Lr As Long

Lr = LastRow(DrList)

Range(A15:H"&Lr,K15:K"&Lr, M15:M"&Lr,............).select

but I got a syntax error.

Would really appreciate some help.

 

[Daniel.C]

Union(Range("A15:H" & Lr), Range("K15:K" & Lr), Range("M15:M" &
Lr)).Select
Daniel

 

[Blubber]

Daniel

You were right about problems pasting non adjacent columns. Decided that I
will define each range separately and paste them one after the other. The
following works for copying the first range "ColAH".

Q: How do I code so that it continues to copy and paste the next ranges
ColK, ColM and so on.... to the next column in destination sheet?

my macro looks like this:

Sub CopyDrList()

Dim TargetWks As Worksheet, SourceWks As Worksheet
Dim TargetCol As Long
Dim ColAH As Range, ColK As Range, ColM As Range
Dim ColN As Range, ColS As Range, ColU As Range

Set SourceWks = Sheets("DrList")
Set TargetWks = Sheets("DrListCal")

Set ColAH = SourceWks.Range("A15:H" & SourceWks.Range("H65536").End(xlUp).Row)

Set ColK = SourceWks.Range("K15:K" & SourceWks.Range("K65536").End(xlUp).Row)

Set ColM = SourceWks.Range("M15:M" & SourceWks.Range("M65536").End(xlUp).Row)

Set ColN = SourceWks.Range("N15:N" & SourceWks.Range("N65536").End(xlUp).Row)

Set ColS = SourceWks.Range("S15:S" & SourceWks.Range("S65536").End(xlUp).Row)

SetColU = SourceWks.Range("U15:U" & SourceWks.Range("U65536").End(xlUp).Row)


'This is finding the next column available in the target sheet.

If TargetWks.Range("A1").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie A)
TargetCol = 1
Else
'cell A1 does have data so find the next available column
TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1
End If

'Start copying data in the first range from the source sheet to the target
sheet

ColAH.Copy TargetWks.Cells(1, TargetCol)

'How do we continue copying the next range (ie: ColK, ColM…. ColU) into the
next empty column in target sheet?

Exit Sub
End Sub

 

[Daniel.C]

Try :

Sub CopyDrList()

Dim TargetWks As Worksheet, SourceWks As Worksheet
Dim TargetCol As Long
Dim ColAH As Range, ColK As Range, ColM As Range
Dim ColN As Range, ColS As Range, ColU As Range

Set SourceWks = Sheets("DrList")
Set TargetWks = Sheets("DrListCal")


Set ColAH = SourceWks.Range("A15:H" &
SourceWks.Range("H65536").End(xlUp).Row)

Set ColK = SourceWks.Range("K15:K" &
SourceWks.Range("K65536").End(xlUp).Row)

Set ColM = SourceWks.Range("M15:M" &
SourceWks.Range("M65536").End(xlUp).Row)

Set ColN = SourceWks.Range("N15:N" &
SourceWks.Range("N65536").End(xlUp).Row)

Set ColS = SourceWks.Range("S15:S" &
SourceWks.Range("S65536").End(xlUp).Row)

Set ColU = SourceWks.Range("U15:U" &
SourceWks.Range("U65536").End(xlUp).Row)


'This is finding the next column available in the target sheet.

If TargetWks.Range("A1").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie
A)
TargetCol = 1
Else
'cell A1 does have data so find the next available column
TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1
End If

'Start copying data in the first range from the source sheet to the
target Sheet

ColAH.Copy TargetWks.Cells(1, 1)
ColK.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColM.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColN.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColS.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColU.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)

End Sub

Daniel

 

[Blubber]

Thanks. That works great!

Try :

Sub CopyDrList()

Dim TargetWks As Worksheet, SourceWks As Worksheet
Dim TargetCol As Long
Dim ColAH As Range, ColK As Range, ColM As Range
Dim ColN As Range, ColS As Range, ColU As Range

Set SourceWks = Sheets("DrList")
Set TargetWks = Sheets("DrListCal")


Set ColAH = SourceWks.Range("A15:H" &
SourceWks.Range("H65536").End(xlUp).Row)

Set ColK = SourceWks.Range("K15:K" &
SourceWks.Range("K65536").End(xlUp).Row)

Set ColM = SourceWks.Range("M15:M" &
SourceWks.Range("M65536").End(xlUp).Row)

Set ColN = SourceWks.Range("N15:N" &
SourceWks.Range("N65536").End(xlUp).Row)

Set ColS = SourceWks.Range("S15:S" &
SourceWks.Range("S65536").End(xlUp).Row)

Set ColU = SourceWks.Range("U15:U" &
SourceWks.Range("U65536").End(xlUp).Row)


'This is finding the next column available in the target sheet.

If TargetWks.Range("A1").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie
A)
TargetCol = 1
Else
'cell A1 does have data so find the next available column
TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1
End If

'Start copying data in the first range from the source sheet to the
target Sheet

ColAH.Copy TargetWks.Cells(1, 1)
ColK.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColM.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColN.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColS.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)
ColU.Copy TargetWks.Cells(1,
TargetWks.Range("IV1").End(xlToLeft).Column + 1)

End Sub

Daniel