Copy all values in a row until blank then copy to another sheet

[markaxtell]

Hi,

I currently have a list of names that will always be a variable number so for example will be column K down to max number of 100 and i want to be able to copy all the names until the blank column and then paste them to another sheet.

Need this to be macro as aware you can do this with specialcells etc

Thanks in advance for help

 

[MARK858]

Try

Sub test2()
Dim LR As Long
LR = Sheets("Sheet2").Range("k1").End(xlDown).Row
Sheets("Sheet2").Range("k1:k" & LR).Copy Destination:=Sheets("Sheet1").Range("C1")
End Sub

Change

 Range("k1:k" & LR).Copy

to

Range("k2:k" & LR).Copy

if you have a header row

HTH

 

[MARK858]

Fogot about the values bit. Try.....

Sub TEST2()
Dim LR As Long
LR = Sheets("Sheet2").Range("k1").End(xlDown).Row
Sheets("Sheet2").Range("k1:k" & LR).Copy
Sheets("Sheet1").Range("C1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub