Trebor8484
Board Regular
- Joined
- Oct 27, 2018
- Messages
- 69
- Office Version
- 2013
- Platform
- Windows
Hi all,
Any ideas why the below piece of code won't copy the desired range.
It should be copying ranges C2:K2 and L2:M2 in the first iteration.
Basically the code should always copy C:K for the relevant row in the loop then either L:M,N:O,P:Q,R:S,T:U depending on which column the loop is currently on.
Thanks
Any ideas why the below piece of code won't copy the desired range.
It should be copying ranges C2:K2 and L2:M2 in the first iteration.
Basically the code should always copy C:K for the relevant row in the loop then either L:M,N:O,P:Q,R:S,T:U depending on which column the loop is currently on.
Code:
Sub MergeMe()
Dim lr As Long
Dim DataSht As Worksheet
Dim c As Range
Dim CopyRange As Range
Dim dstsht As Worksheet
Set DataSht = Sheets("Data")
Set dstsht = ThisWorkbook.Sheets("Merged")
lr = DataSht.Range("A" & Rows.Count).End(xlUp).Row
For Each c In DataSht.Range("L2:L" & lr & ",N2:N" & lr & ",P2:P" & lr & ",R2:R" & lr & ",T2:T" & lr)
If c.Value <> "" Then
Set CopyRange = Union(DataSht.Range(Cells(c.Row, "C"), Cells(c.Row, "K")), DataSht.Range(Cells(c.Row, c.Column), Cells(c.Row, c.Offset(0, 1))))
CopyRange.Copy
dstsht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next c
End Sub
Thanks