IF '???' copy to other workbook [SOLVED]

This is your solution

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub


If Target.Value <> 1 And Target.Value <> 2 Then Exit Sub


If Target.Value = 1 Then
    Target.EntireRow.Copy
    With Sheets("Veh1")
    .Activate
    .Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End With
    Sheets("Data").Activate
    Target.Offset(0, -3).Select
Else
    Target.EntireRow.Copy
    With Sheets("Veh2")
    .Activate
    .Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End With
    Sheets("Data").Activate
    Target.Offset(0, -3).Select


End If


End Sub

also attached the sheet.

:cheers:

I take liberty to paste a long code here.

As per the requirement of perryco sent by U2U, he is handling 40 vehicles and the data needs to be entered in the appropriate sheets based on value entered in col D.

The following code will :
1. See if the value in col D is valid (1 to 40)
2. See if the required sheet is available?
3. If not add the sheet from sample sheet
4. Copy the data in appropriat place
5. Put a date time stamp in front of the data

******************************************
Private Sub Worksheet_Change(ByVal Target As Range)
'This code will check the value in D, based on the number
'will paste the data in the sheet pertaining to it.
Dim sht As String
Dim s As Worksheet
Dim avialable As Boolean
Dim decision As VbMsgBoxResult
avialable = False

'========= Part I : checking range and data entered =========
'This will check if the value changed is in D column
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

'This will check if the value is numeric and within the range of 1 to 40

If Target.Count <> 1 Then
MsgBox "The changed cells are more than one, can handle one at a time :("
Exit Sub
End If

If IsNumeric(Target.Value) = False Or _
Target.Value < 1 Or _
Target.Value &gt; 40 Then Exit Sub

'This will create the sheet name to copy the data
sht = "Veh" & Target.Value

'========= Part II : checking if the sheet exists =========
'This will check if the sheet is available
For Each s In ActiveWorkbook.Sheets
If s.Name = sht Then avialable = True
Next

'This will create the sheet if not available and name as per the vehicle #
If avialable = False Then

decision = MsgBox("The Sheet " & sht & _
" is not available, do you want to add it?", vbYesNo)
If decision = vbNo Then Exit Sub

Sheets("sample").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = sht
avialable = True
Sheets("Data").Select
End If

'========= Part III : Actual copy-paste and time stamping =========
Application.ScreenUpdating = False
Sheets("Data").Select
Target.EntireRow.Copy
With Sheets(sht)
.Activate
.Range("A65536").End(xlUp).Offset(1, 0).Select
.Paste
Application.CutCopyMode = False
End With
Sheets("Data").Activate
Target.Offset(0, 1).Value = "The record is added in Sheet : " & sht & " on : " & Now()
Target.Offset(0, -3).Select
Application.ScreenUpdating = True

End Sub

******************************************

Thanx to perryco for asking cristle clear question. This is one of really good codes i wrote for some time.

Anyone please take liberty to jump in and suggest changes

Also a file attached

Hi,

This code is nearly the solution to something I am trying to do.

I want to do the same conditional copying but my needs are a bit more complicated. I need to:

*Look at text rather than numbers (eg "Vehicle One" rather than "1")

*Copy only some fields from the row

* There is a further condition - I only want to copy the rows that I entered that day.

Can anyone help?

Thanks!

:puzzled:

*Look at text rather than numbers (eg "Vehicle One" rather than "1")

-&gt; If the number of vehicles is small say 5-10 then this is a good idea as well.
You can create a data validation drop down list, use the code i gave first and use "select case" instead of "if = 1" etc.

Let me know if you are not comfortable with Select Statements.

*Copy only some fields from the row

Yes, very much true:: Following code will
copy only 4 cells in that row (Target and three before that)

Range(Target.Offset(0, 3), Target).Copy

replace the copy statement with this or an amended version of this!!!

* There is a further condition - I only want to copy the rows that I entered that day.

Humm.... This is interesting now.
What you will need to do is that::: in some cell in the row, you will need to put the change date in that cell in every row.

ActiveSheet.Cells(Target.Row, 100).Value = Now()

This statement at the start of the code in
Sub Worksheet_Change(ByVal Target As Range) will enter the date (time as well)
You will need to compare it at the time of transfer.

Hope this helps you.

Regarding the last point, if you find it difficult to convert, post back with sufficient details!!!

Hi,

Thanks for the below. It makes vague sense to me, but due to being generally useless I need more help!!

*Select Statements - Err, all I can say is that I tried. But failed. See attached.

*Target.Offset. ??? How would I copy, say, cells 4, 5, 8 then 2?

*TODAY(). The data that I want to copy comes itself from a form-style worksheet. I have incorporated TODAY() into the form so that the database now contains the entry date. But how would I look at both the entry date and the Company division (vehicle name)?

Feeling truly dense now! Your help and patience appreciated.

MKNov

Hi MKNOV,

Thanx for your sincere appereciation of a solution which solved the problem of the original query of perryco, by saying ...

Quote

It makes vague sense to me, but due to being generally useless I need more help!!

As the original post is solved, i do not want to complicate this post.

Please create another post with details of your problem, and we will make sure that it will not be a "generally useless" solution.

Regards,

Err, not sure but I think you may have misunderstood - It is of course me that is 'generally useless', and not, obviously, your very intuitive solution.

But yes, point taken, and I agree - a new post seems the best idea.

On it's way...