VBA use next column if column is empty

Bob1978

New Member
Joined
Jan 25, 2013
Messages
29
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

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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top