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
Any help would be very much appricated,
Thanks in advance
Bob
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
VBA Code:
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