Moving data from one cell to another based on month selected

DezB · Jan 19th 2016

Hi Folks

I'm trying to set up a document that once a month a user enters data into a column. (that's the easy bit...)
Then, the following month the user changes the month using a drop down list, (again not rocket science..)

However what i'm trying to do without much success is have the data that was entered move to the left into another column allowing the user to enter new data into the new months column.

i've attached an anonymised version and would be exceedingly grateful for any assistance please.

Kind regards

DezBforum.ozgrid.com/index.php?attachment/68023/

**Carim** · Jan 19th 2016

Re: Moving data from one cell to another based on month selected

Hi,

In essence you need to move your data to Column C ..., you could test following :

Code

Sub DataMove()
Range("D8:D10").Cut Destination:=Range("C8")
Range("D14:D21").Cut Destination:=Range("C14")
Range("D26:D30").Cut Destination:=Range("C26")
Range("D34:D39").Cut Destination:=Range("C34")
Range("D44:D50").Cut Destination:=Range("C44")
End Sub

The second question would be do you need a worksheet event macro whenever cell B3 is modified ...?

HTH

DezB · Jan 19th 2016

Re: Moving data from one cell to another based on month selected

Hi Carim

Thanks for getting back to me so soon, much appreciated. I'd looked at using a function such as you mentioned but really wanted it to be automatic, i.e. when the month is changed it moves the data without having the user having to anything else. Some, (most!) of my users wouldn't remember to press the button!! I'm afraid.

Kind regards

DezB

M40wen · Jan 19th 2016

Re: Moving data from one cell to another based on month selected

Does this work for you? Needs to be added into the sheet code. Added a message box to confirm - if your users are dozy enough to not click a button you can be sure that they will press it when they don't mean to!

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Select
rw = Target.Row
cl = Target.Column


If rw = 3 And cl = 2 Then
Ans = MsgBox("Do you want me to move data for you?", vbYesNo)
Select Case Ans
Case vbYes
Columns(4).Copy
Columns(4).Select
Application.EnableEvents = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns(5).Insert
rw = 1
LstRw = ThisWorkbook.Sheets("Rationale").UsedRange.Rows.Count
Do Until rw > LstRw
If ThisWorkbook.Sheets("Rationale").Cells(rw, 6) = "Rationale" Then
ThisWorkbook.Sheets("Rationale").Cells(rw, 5) = "=B3"
End If
rw = rw + 1
Loop
Columns(3).Delete
Application.EnableEvents = True
 
Case vbNo
'do nothing
End Select


End If
 


End Sub

Display More

DezB · Jan 19th 2016

Re: Moving data from one cell to another based on month selected

HI M40wen

Many thanks, I'll try this when I get back to work in the morning. Yes they probably would be dozy enough to click any button without fully understanding what they're doing...

In their defence though they are extremely busy people which is why I'm trying to make it as automated as possible.

Kind regards

DezB

DezB · Jan 20th 2016

Re: Moving data from one cell to another based on month selected

HI M40wen

This is almost spot on except that the only cells that need to be moved are the ones where data is entered, i.e. D8:D10 go to C8:C10, D14:D21 go to C14:C21, D26:D30 go to C26:C30, D34:D39 go to C34:C39 and D44:D50 go to C44:C50.

I did try playing around with the code but got hopelessly lost I'm afraid.

Really appreciate your help with this.

Kind regards

DezB

M40wen · Jan 20th 2016

Re: Moving data from one cell to another based on month selected

Hello DezB,

Does it matter that it moves the whole column? Basically the code is copying and pasting as text, inserting a new column to the right and then deleting the old column out to the left. Do you have data below it that you don't want moved? In my experience rows get added over time and the code will handle this... If you can explain why this is an issue I can adapt the code for you

DezB · Jan 20th 2016

Re: Moving data from one cell to another based on month selected

Hi M40wen

At the moment there is no data below what is already there, however what I can't guarantee is that they won't want to add more at a later date.. I'm sure you know how it is, once thay like using something someone always asks "can we add this or that in?"

Hope this helps.

Kind regards

DezB

M40wen · Jan 20th 2016

Re: Moving data from one cell to another based on month selected

Sorry had a meeting!

Ok so the cell references you mention - is it only ever going to be those ranges and no extra rows added over time? I normally try and make the range dynamic, but for that Id need to understand what the cut off point is. Alternatively will just hard code it for that range for you...

**Carim** · Jan 20th 2016

Re: Moving data from one cell to another based on month selected

Hi,

You can have the macro triggered automatically ...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
Application.Run ("DataMove")
End Sub

HTH

M40wen · Jan 20th 2016

Re: Moving data from one cell to another based on month selected

Hi,

Im not sure whether you want the new month cleared out or not, but this code will do that for you. Im not sure this is how I would design the spreadsheet to work but hopefully it gives you an idea. Post back if not. Remember the change event will trigger

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Select
    rw = Target.Row
    cl = Target.Column
     
    If rw = 3 And cl = 2 Then
        Ans = MsgBox("Do you want me to move data for you?", vbYesNo)
        Application.EnableEvents = False
        
        Select Case Ans
        Case vbYes
            Columns(4).Copy
            Columns(3).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            'Put this as the first row to start checking from
            MyRw = 7
            'Find the last row of data
            LstRw = ThisWorkbook.Sheets("Rationale").UsedRange.Rows.Count
            'You havent asked but I assume you want the old value now cleared out so loop this through. Rather than LstRw, you could just specify the row number to stop
            Do Until MyRw > LstRw + 1
                
            'you could use this is you prefer
            'If ThisWorkbook.Sheets("Rationale").Cells(rw, 4) = "R" or If ThisWorkbook.Sheets("Rationale").Cells(rw, 4) = "A" or If ThisWorkbook.Sheets("Rationale").Cells(rw, 4) = "A/G" or If ThisWorkbook.Sheets("Rationale").Cells(rw, 4) = "G" then
                If ThisWorkbook.Sheets("Rationale").Cells(MyRw, 4).Formula <> "=B3" Then
                   ThisWorkbook.Sheets("Rationale").Cells(MyRw, 4).Clear
                   ThisWorkbook.Sheets("Rationale").Cells(MyRw, 4).ClearFormats
                Else
                'means row is the month
                Mnth = UCase(ThisWorkbook.Sheets("Rationale").Cells(MyRw, 4).Value)
                Select Case Mnth
                Case "JAN"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "DEC"
                Case "FEB"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "JAN"
                Case "MAR"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "FEB"
                Case "APR"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "MAR"
                Case "MAY"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "APR"
                Case "JUN"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "MAY"
                Case "JUL"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "JUN"
                Case "AUG"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "JUL"
                Case "SEP"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "AUG"
                Case "OCT"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "OCT"
                Case "NOV"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "NOV"
                Case "DEC"
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "DEC"
                Case Else
                ThisWorkbook.Sheets("Rationale").Cells(MyRw, 3) = "MONTH?"
                End Select
                
                End If
                MyRw = MyRw + 1
            Loop
           
             
        Case vbNo
             'as this has been changed, the month may need to be changed "back".       
             
        End Select


    Application.EnableEvents = True


         
    End If
     
     
End Sub

Display More

DezB · Jan 21st 2016

Re: Moving data from one cell to another based on month selected

Hi M40wen

Unfortunately, again I'm wouldn't guarantee that they may or may not want to add in more criteria at some point, (it wouldn't surprise me if they did want to...) The point is, that as they change from one month to another each time the sheet is completed they can see the previous months data for comparison. I'm tied down to the design/layout as it was already chosen by them I'm afraid.

Hope this helps.

Kind regards

DezB

**Carim** · Jan 21st 2016

Re: Moving data from one cell to another based on month selected

Hi DezB,

The combination of the DataMove() macro along with the Change event macro should do the job ... No ?

M40wen · Jan 21st 2016

Re: Moving data from one cell to another based on month selected

Hi,

Doesn't the revised code give you what you need? The code should adapt to rows as necessary, and will copy from one cell to the other unless it is formulae "=B3". Ive given you more than what you asked for as I know what people in offices do, but adapt as you see fit!

DezB · Jan 25th 2016

Re: Moving data from one cell to another based on month selected

Hi M40wen and Carim

Apologies for the delay in replying to you, I had a couple of days off I've tried both methods now with the following results:

Carime, works a treat except for it changes the Conditional Formating that alters the cell colour dependant on the data entered, and for some reason it also changes the cell colour of the month cell. Can't figure that one out as the cell references in the module code don't relate to those cells??

M40wen, again works a treat but again changes the Conditional Formatting, it seems to remove it from column D altogether

I really appreciate both your help here and have to admit that it is proving a steep but highly enjoyable learning curve for me.

Kind regards

DezB

**Carim** · Jan 25th 2016

Re: Moving data from one cell to another based on month selected

Hi DezB,

At least the core issue is working fine ...

Regarding the Conditional Formatting ... it appears as a new requirement ...

Could you possibly attach your workbook in your next message ... :smile:

Cheers

DezB · Jan 25th 2016

Re: Moving data from one cell to another based on month selected

Hi Carim

Will do.

Kind regards

Deryn

forum.ozgrid.com/index.php?attachment/68085/

DezB · Jan 25th 2016

Re: Moving data from one cell to another based on month selected

Hi Carim

I've also attached a version with the VB code, hope it helps.

Kind regards

DezB

forum.ozgrid.com/index.php?attachment/68086/

**Carim** · Jan 25th 2016

Re: Moving data from one cell to another based on month selected

Hi DezB,

Thanks for both files ...

You have selected the long version of the event macro ...

Having your four CF rules extended to the whole range C8:D50, you could have source data copied from Column D to Column C and then clear the contents of Column D for the new input... This would prevent the disruption of your 4 CF rules ...

HTH

DezB · Jan 25th 2016

Re: Moving data from one cell to another based on month selected

Hi Carim

Ah hah, I'll have a go, I did wonder whether breaking the Conditional formatting into blocks was causing a problem. I shall let you know my findings.....

Kind regards

DezB

Moving data from one cell to another based on month selected

Participate now!

Share

Users Viewing This Thread