VBA use next column if column is empty

[Bob1978]

Hi,

I really hope you can help me, I use an excel spreadsheet to track progress in my project, each week I need to cut cells from sheet 1 Column B and shift cells left. The range is B6 to B11, Cell B6 has a number in it, the rest have colours.

The copied data should then be pasted in two sheet 2, this is where I am stuck as I would like each time a paste is done that the paste is done in the next available column.

Therefore I would end up with an archive each week of range B6:B11 next to each other e.g. week 1 pasted to Column B, week 2 pasted to Column C, week 3 to column D etc.

I have the attached code, but sadly all this does if paste below the number I have in the cell in column B and doesn’t move to the next column e.g. column C

Range("B6:B11").Select

Selection.Cut

Sheets("Sheet2").Select

Range("B" & Rows.Count).End(xlUp).Offset(1).Select

ActiveSheet.Paste

Sheets("Sheet1").Select

Range("B6:B11").Select

Selection.Delete Shift:=xlToLeft

Any help would be very much appricated,

Thanks in advance

Bob

 

[DanteAmor]

Try this:

Sub copydata()
  With Sheets("Sheet1").Range("B6:B11")
    .Copy Sheets("Sheet2").Cells(6, Columns.Count).End(1).Offset(0, 1)
    .Delete Shift:=xlToLeft
  End With
End Sub

 

[Bob1978]

Thank you very much DanteAmor

This worked perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.