How to search and store a column based on a single value in it?

Hi there,

I have a drop-down with the 12 months of the year (Refer to the attached image - it's not much, but should paint a clear picture). Since we are in August, the Year to Date Actual Column ("YTD Actual") comes after the month we are in, i.e. to the right of it.

Depending on the month selected from a drop-down I want to somehow dynamically identify where the column with the "||" is (Column C in the picture), cut it from its current location (whichever one it may be), and paste it to the right of the column for the current month selected.

I need this to work going forward and backwards, therefore, a simple pre-recording will not work, I need some logic and code

Example walkthrough:
1) Open the spreadsheet for July, YTD Actual column is B:B
2) Select August from drop-down
3) Macro begins - Find where YTD Actual column is
4) Cut it
5) Paste it to where you can see it in the picture C:C

So my issues are:

1) How to search for "||" and select the column which has that cell and activate it (and be able to refer to it again for the next time I pick from the drop-down)
2) Cut it and Paste it after the month the drop-down is displaying

I have only as much experience with VB as much as someone who just read a VBA Book for Dummies.

Thank you very much for all your help

Microsoft Excel - Book1.png

Well the drop-down is a simple data validation list, but it can be changed to whatever else, I don't believe that's the bottleneck. I attached an image, I can't attach a sample book because it would take me too long to erase all the data which is confidential.

This is what I have in terms of code so far:
Dim month As String
month = ActiveWorkbook.Sheets("DATE").Range("C1").Text
If InStr(1, month, "August") Then
Sheets(Array("X1", "X2", "X3")).Select
Sheets("X1").Activate
Columns("E:E").Select
Selection.Cut
Columns("G:G").Select
Columns(“F:F”).Insert Shift:=xlToRight
End If

But as I said, I want to avoid selecting and cutting a specific column but rather search for it (either looking for "||" or anything else) select it and move it to a different location and maybe have a case statement for each month...?

Drop-down.png

Originally Posted by XeRo Solus

What cell is the data validation list in?

Why are you focusing so much on the drop-down or the list?

It's a simple list of the 12 months of the year. Nothing more to it.

I'm looking for help with code syntax and logic in VB, not how to make a drop-down in Excel

Originally Posted by XeRo Solus

I thought you wanted to launch the macro by a selection in that drop down list.

OK, sure, I do.

What now? Any ideas about the code?

Thanks

Thank you for your help. Not everything has to be literal. The drop-down menu can be anywhere - I can always move it after. But in the actual Excel Workbook I'm using that I can't share, in a spreadsheet called "DATE", in a cell C1 there is a dropdown with "January", "February", ..., "December".

You can use any symbol, but yes, I just so happen to have the 2 pipe symbols above Enter, i.e. "\" with Shift.

My logic is that I will have a Sub which will look for a given symbol, find it in the YTD column, select it, cut it from its current location, and paste it to the right of another column depending on the month selected from the drop-down.

It would be incredibly wonderful if this could work that way. What do you think Xero Solus?

I can't thank you enough for all your help today but I still can't run it. In the spreadsheet you attached, the macro is not in it. When I insert a module and paste the code to run it, I get 'Object required' run time error pointing to this line:
If Not Intersect(Target, Range("A2")) Is Nothing Then

Is the workbook you attached suppose to be functional?

Appreciate all your help!

OMG, it is working now, this is genius. Thank you.

Actually, I didn't want to complicate things from the beginning, but what if the drop-down with the month was on one sheet, and the column that you were cutting and inserting was on another sheet, and not only did you have to select that one column, but you also had to select, cut and insert that same column on 3 different sheets at the same time (the columns are all in the same position on all 3 sheets)...

Well, I didn't want to make this thread so specific to my issue from the beginning so that other can benefit from it. That's why I tried to keep it more generalized.

But here's an example workbook with all the sheets and formatting that I will need. First sheet has the month drop-down, the next 3 are identical and all need to have the same logic for the whole process that you have already nailed (i.e. finding, cutting, inserting)

Words cannot express how much I appreciate your help, I did not expect so much help from this forum, especially from one person.

Originally Posted by XeRo Solus

Glad to help

This worked in the sample workbook you provided. However, I had to make sure that Range B1:N1 on each sheet was a date formatted Jul-13 and not just JUL-13 typed into the cell as a string.

Attachment 256687

Apologies for the late reply. Thank you thank you thank you! You are the most helpful person-stranger I have ever known Your solution works perfectly

Good luck and take care

Originally Posted by XeRo Solus

It was fun. I enjoy the problem solving. I'm glad you were able to find what you needed at EF.

I appreciate the reputation, if you wouldn't mind marking the thread as [SOLVED] using the thread tools at the top.

Actually, there was one more question I had: I noticed that when the macro is run, any formulas that were in the column we are moving, are lost. I can't even write any text in it, as it gets lost every time the month is changed. I want to be able to have data in that column with formulas with references and still keep them after a change in the month.

Is it possible to keep the integrity of the column, i.e. to cut and paste instead of deleting it?

Although it functions properly, it really can't be used in a real-world scenario as all the data in the column is wiped when the macro moves it to a new location.

Thank you one more time

Originally Posted by XeRo Solus

I'm sorry. I didn't see the formulas. Are they in the YTD column in the red area except for the || symbol?

Hi again,

Well, you were right to not see any of the formulas because they were not actually in the sample spreadsheet, but in reality, for the real spreadsheet, it is pretty much safe to assume that every cell except for the row 11 (where all the months are) and of course the "||" in the target column has some form of a SUMIF or another reference/calculation formula.

If I'm not mistaken changing the line with cel.EntireColumn.Delete to cel.EntireColumn.Cut should solve the issue from the quick testing I did today. Is that completely correct?

Thank you!

Originally Posted by XeRo Solus

Try replacing the code we came up with early with this:

Please Login or Register  to view this content.

Hi again,

As always I appreciate your help, but I keep running into more and more issues for which I need to ask for a little bit more help:

When I change the month, and the macro runs, it changes the column; however, if there is a formula pointing to a cell in that column, I get reference error (#REF) after a change the month.

When you have cells which are moving, how do you reference them so the formula doesn't break after the macro is run? Do you have to do it in Visual Basic?

I have attached a spreadsheet to demonstrate the problem and what happens after one change in the month drop down.

Thank you a million times!