VBA Code for Shifting Data in Cells based on "Start Month"

pinkcookie · Mar 23, 2016

I'm trying to write a VBA code that shifts the data of a cell based on the start month, so for example:

if the start month is March then the "data" set starts in March (and there's a 12 month table, starting from Jan. to Dec., each month is a column, the start month will be indicated as a text/string before the 12 columns, on the same row)

I want to make it such that if I change the start month to, let's say June, then the data will shift 3 cells to the right.

I've tried using offset and match functions on excel and I tried coding it in vba but I'm having no luck.

Sorry if the formating for this question is weird, it's my first time posting for excel help!

Steve_R · Mar 23, 2016

Hi Pincookie, welcome to the forum.
It is hard to understand precisely what you want without seeing a sample but I suspect you may be after something like the following?

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Data	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	15/01/2017	VPG	VPG
3	16/06/2016	LES						LES
4	29/07/2016	PUK							PUK
5	17/03/2017	EHK			EHK
6	9/06/2016	HCB						HCB
7	13/11/2016	BLI											BLI
8	19/09/2016	IXD									IXD
9	7/10/2016	LWY										LWY
10	19/02/2017	TYX		TYX
11	27/08/2016	SQF								SQF
12	14/08/2016	IRN								IRN
13	5/09/2016	KPS									KPS
14	6/05/2016	FVQ					FVQ
15	6/05/2016	UUA					UUA
16	2/10/2016	QYR										QYR
17	15/12/2016	ISZ												ISZ
18	18/09/2016	CQP									CQP
19	27/03/2016	IBH			IBH
20	29/11/2016	NIK											NIK
21	1/10/2016	KPN										KPN

Sheet1

Worksheet Formulas

Cell	Formula
C2	=IF(TEXT($A2,"mmm")=C$1,$B2,"")

Drag or copy the formula to other cells.

If that doesn't do what you want, post a small sample showing what you have and how it should look after. At the bottom of my signature is a link where you will find instructions for posting aids (I used Mr Excel HTML Maker above).

pinkcookie · Mar 23, 2016

Steve_R said:
Hi Pincookie, welcome to the forum.
It is hard to understand precisely what you want without seeing a sample but I suspect you may be after something like the following?

Original data (the numbers are the "data")

A B C D E F G H I J K L M N
1 Start Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 Mar 15 15 15 15
3 Jan 10 10 10 12 12 12
4 Sep 65 67 33

<tbody>
</tbody>

New version - after changing the start months (for the first one only)

A B C D E F G H I J K L M N
1 Start Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 Jul 15 15 15 15
3 Jan 10 10 10 12 12 12
4 Sep 65 67 33

<tbody>
</tbody>

Drag or copy the formula to other cells.

If that doesn't do what you want, post a small sample showing what you have and how it should look after. At the bottom of my signature is a link where you will find instructions for posting aids (I used Mr Excel HTML Maker above).

Hi! Thanks for replying, sorry I didn't know how to get a sample of my sheet up here $:

But I've modified what you posted to look more like what I'm looking for...

From the original to the new version, I've changed the "Start Month" from Mar to Jul, and so the data that was originally from Mar - Jun shifts to Jul - Oct...

Was this a bit more clear in what I was looking for?

Thanks in advance for looking into this!

Peter_SSs · Mar 24, 2016

Welcome to the MrExcel board!

pinkcookie said:
sorry I didn't know how to get a sample of my sheet up here

Steve already pointed you to that information.

Steve_R said:
At the bottom of my signature is a link where you will find instructions for posting aids (I used Mr Excel HTML Maker above).

(There's one in my signature block below as well.

)

Steve_R · Mar 24, 2016

pinkcookie said:
Hi! Thanks for replying, sorry I didn't know how to get a sample of my sheet up here $:

But I've modified what you posted to look more like what I'm looking for...

From the original to the new version, I've changed the "Start Month" from Mar to Jul, and so the data that was originally from Mar - Jun shifts to Jul - Oct...

Was this a bit more clear in what I was looking for?

Thanks in advance for looking into this!

The hard part at this end is understanding what you have, your 'rules' and what your plan is. The more detail you are able to provide, the easier it is for people to help you. A HTML posting aid helps us see the exact layout you have in your sheet. For example, we can see dates and what column all data are in, which would reduce the chance of writing code that works with incorrect formats or columns. One thinkg we don't want to see is sensitive data so change anything sensistive.

Interpretting what you posted above, I have some questions:
1) If you change the month in column A (for example) from Mar to Aug, copy everything that currently exists in Mar, April, May, etc and make the new start column I (Aug).
2) What if data in you table would extend beyond Dec when moved?
3) Are there likely to be any data left of the month (e.g. you have Apr in column A but have data in B,C,D)?
4) Are any data below the table or is everything blank (thinking we may need to use a worksheet change event, intercepting change in column A to automate the process)?

pinkcookie · Mar 24, 2016

Steve_R said:
The hard part at this end is understanding what you have, your 'rules' and what your plan is. The more detail you are able to provide, the easier it is for people to help you. A HTML posting aid helps us see the exact layout you have in your sheet. For example, we can see dates and what column all data are in, which would reduce the chance of writing code that works with incorrect formats or columns. One thinkg we don't want to see is sensitive data so change anything sensistive.

Interpretting what you posted above, I have some questions:
1) If you change the month in column A (for example) from Mar to Aug, copy everything that currently exists in Mar, April, May, etc and make the new start column I (Aug).
2) What if data in you table would extend beyond Dec when moved?
3) Are there likely to be any data left of the month (e.g. you have Apr in column A but have data in B,C,D)?
4) Are any data below the table or is everything blank (thinking we may need to use a worksheet change event, intercepting change in column A to automate the process)?

Original data (the numbers are the "data")

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Item	Start Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Task One	Mar			15	15	15	15
3	Task Two	Jan	10	10	10	12	12	12
4	Task Three	Sep									65	67	33

After switching the value in B2 from Mar to Aug

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Item	Start Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Task One	Mar			15	15	15	15
3	Task Two	Jan	10	10	10	12	12	12	13	13
4	Task Three	Sep									65	67	33

After switching the value in B3 from Jan to Sep

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Item	Start Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Task One	Mar			15	15	15	15
3	Task Two	Sep					10	10	10	12	12	12	13	13
4	Task Three	Sep									65	67	33

2) It's alright if the data extends out after Dec as there would be nothing to the right of the sheet. But if it's possible to make the max stop at Dec that would be good too (but that would mean removing the content after the Dec column, but I'd want to retain any of the content cleared somehow,so that if I called an earlier month after, I'd still be able to produce the data again - as shown above)

3) There will never be any data left of the starting month.

4) The worksheet will be blank aside from this table.

Thanks!</div>

pinkcookie · Mar 24, 2016

pinkcookie said:
Original data (the numbers are the "data")

A B C D E F G H I J K L M N
1 Item Start Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 Task One Mar 15 15 15 15
3 Task Two Jan 10 10 10 12 12 12
4 Task Three Sep 65 67 33

<tbody>
</tbody>

After switching the value in B2 from Mar to Aug

A B C D E F G H I J K L M N
1 Item Start Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 Task One Mar 15 15 15 15
3 Task Two Jan 10 10 10 12 12 12 13 13
4 Task Three Sep 65 67 33

<tbody>
</tbody>

After switching the value in B3 from Jan to Sep

A B C D E F G H I J K L M N
1 Item Start Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 Task One Mar 15 15 15 15
3 Task Two Sep 10 10 10 12 12 12 13 13
4 Task Three Sep 65 67 33

<tbody>
</tbody>

2) It's alright if the data extends out after Dec as there would be nothing to the right of the sheet. But if it's possible to make the max stop at Dec that would be good too (but that would mean removing the content after the Dec column, but I'd want to retain any of the content cleared somehow,so that if I called an earlier month after, I'd still be able to produce the data again - as shown above)

3) There will never be any data left of the starting month.

4) The worksheet will be blank aside from this table.

Thanks!

I forgot to answer the first question, that's correct, when Mar changes to Aug, all the values from Mar, Apr, May, etc. is copied to Aug, Sep, Oct, etc

Peter_SSs · Mar 24, 2016

pinkcookie said:
2) It's alright if the data extends out after Dec as there would be nothing to the right of the sheet. But if it's possible to make the max stop at Dec that would be good too (but that would mean removing the content after the Dec column, but I'd want to retain any of the content cleared somehow,so that if I called an earlier month after, I'd still be able to produce the data again - as shown above)

There's a bit of circling around there, but if you are happy to have the excess hang out to the right so it can be retrieved, try this in a copy of your workbook.

This would go in the worksheet's code module.

Rich (BB code):

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rData As Range
  Dim newCol As Long
  
  If Target.Count > 1 Then Exit Sub
  If Target.Column = 2 And Target.Row > 1 Then
    On Error Resume Next
    Set rData = Target.Offset(, 1).Resize(, 12).SpecialCells(xlConstants).Cells(1)
    newCol = Range("C1:N1").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    If Not rData Is Nothing And newCol > 0 Then
      Application.EnableEvents = False
      rData.Resize(, 23).Cut Destination:=Target.Offset(, newCol - Target.Column)
      Application.EnableEvents = True
    End If
  End If
End Sub

If you don't want to see any of the data hanging out to the right of the table, just hide columns O:Y

VBA Code for Shifting Data in Cells based on "Start Month"

pinkcookie

New Member

Excel Facts

Steve_R

Active Member

pinkcookie

New Member

Peter_SSs

MrExcel MVP, Moderator

Steve_R

Active Member

pinkcookie

New Member

pinkcookie

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

VBA Code for Shifting Data in Cells based on "Start Month"

New Member

Excel Facts

Active Member

New Member

MrExcel MVP, Moderator

Active Member

New Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock