Rock my World

rascl5

New Member
Joined
Mar 19, 2009
Messages
9
Need to know how to use a 'date field', specifically in a chosen column, (lets say column C) that will transfer an entire row of information to another tab - next in line.
Example 03/19/09 to the 'tab/worksheet' labeled 03?
Is this possible?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Would there be a difference if I was to automate a date?

Yes there would. Try this for manually entered dates. Right click the tab of the sheet where you are entering dates, select View code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Column = 3 Then
    Application.EnableEvents = False
    With Sheets(Format(Month(Target.Value), "00"))
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Yes there would. Try this for manually entered dates. Right click the tab of the sheet where you are entering dates, select View code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Column = 3 Then
    Application.EnableEvents = False
    With Sheets(Format(Month(Target.Value), "00"))
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub


Will give a try - but may need to wait till tomorrow - can I reach out then to you?
 
Upvote 0
Will give a try - but may need to wait till tomorrow - can I reach out then to you?


Sure you can. One thing I forgot to mention is that this code expects sheets 01 ... 12 to exist. If they don't it will crash.
 
Upvote 0
Me again!
Thanks again for your help and patience.
I had previously made the 01-thru 12 sheets and had copied your code, however I am unsure as to how to pinpoint the information needed to export the data. The date itself is in column F (6) and I need to export columns A thru R (1-18) to the appropriate 01 thru 12 sheets based upon the Month of the entered date in column F (6).... Any more light to shed on this would be much appreciated VoG!
 
Upvote 0
Yes there would. Try this for manually entered dates. Right click the tab of the sheet where you are entering dates, select View code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Column = 3 Then
    Application.EnableEvents = False
    With Sheets(Format(Month(Target.Value), "00"))
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub



Me again!
Thanks again for your help and patience.
I had previously made the 01-thru 12 sheets and had copied your code, however I am unsure as to how to pinpoint the information needed to export the data. The date itself is in column F (6) and I need to export columns A thru R (1-18) to the appropriate 01 thru 12 sheets based upon the Month of the entered date in column F (6).... Any more light to shed on this would be much appreciated VoG!
 
Upvote 0
Try

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Column = 6 Then
    Application.EnableEvents = False
    With Sheets(Format(Month(Target.Value), "00"))
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & Target.Row & ":R" & Target.Row).Copy Destination:=.Range("A" & LR + 1)
    End With
    Target.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top