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

  • I have been struggling with this for some time. There are two workbooks, one for entering all data (expenditures in the attachment), the other to show entries just for one category (vehicle1, vehicle2 in the attachment).

    If an entry is made on 'expenditure' in columns E G L M N & S, with the entry in S being '2', then copy the entry to the next empty row in its own workbook 'vehicle2'

    Kind of like 'IF S=1 copy to vehicle1, but IF S=2 copy to vehicle2 and so on.

    Suggestions most appreciated.


  • This is your solution

    also attached the sheet.


    Thanks: ~Yogendra

  • 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

    '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
    End If

    '========= Part III : Actual copy-paste and time stamping =========
    Application.ScreenUpdating = False
    With Sheets(sht)
    .Range("A65536").End(xlUp).Offset(1, 0).Select
    Application.CutCopyMode = False
    End With
    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

    Thanks: ~Yogendra

  • 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?



  • *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!!!

    Thanks: ~Yogendra

  • 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.


  • Hi MKNOV,

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


    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.


    Thanks: ~Yogendra

  • 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...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!