copy and paste without header

[andysh]

Hi,

I'm trying to copy and parse some data from colB to colC. I've recorded the below and it's fine but I'd like to leave the column headers intact.

I've tried changing the range to ("B2:B") and ("C2:C") but this doesn't seem to work. Looking around this and other sites it doesn't seem like an incorrect way of setting the range. Clearly there is some nugget of information I can't glean from forums which I need in order to be able to understand why it's incorrect and how to solve it.

Any advice would be much appreciated.

Range("B:B").Select
Selection.Copy
Range("C:C").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="@", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True

 

[jasonb75]

I can still see one reference to C1 in your code, also selection is not necessary and could be part of the problem if it is not doing as expected. Perhaps this will work.

Dim lRow As Long
lRow = Cells(Rows.Count,2).End(xlUp).Row
Range("B2:B" & lrow).Copy Range("C2:C"& lRow)
Range("C2:C" & lRow).TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="@", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True

 

[andysh]

Thanks, Jason, works a treat.