VBA: Paste column range in another column in the first empty cell

[wjeil]

Hello,

I have a question, and hopefully someone can help me out.

Range("A2:A5").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Range("A2:A5").Select
Selection.ClearContents
Sheets("Input_Batches").Select

I want to adjust the above copy paste command in VBA, so that it selects the first empty column of F. So that means if F1 till F10 e.g. already have a value/text. It has to copy the range in F11.

 

[Joe4]

You can also simplify your code a bit by getting rid of all those SELECTs, which are not necessary, i.e.

Range("A2:A5").Copy Cells(Rows.Count, "F").End(xlUp).Offset(1,0)
Range("A2:A5").ClearContents

or even simpler yet, just this one line of code (using CUT instead of COPY, since you want to clear the original data anyway):

Range("A2:A5").Cut Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)

 

[wjeil]

Perfect thanks. And how if i want to copy the range to another worksheet (tab)?

 

[Joe4]

Perfect thanks. And how if i want to copy the range to another worksheet (tab)?

I think you should just be able to preface the copy range with the sheet name, i.e.

Range("A2:A5").Cut Sheets("sheetname").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)