How to copy data automatically to particular sheet?

nicholes · #1 03-07-2014, 10:14 PM

I want to copy data of a cell to a particular sheet.

I have three sheets one main sheet and two of customers A and B>For example I have two customer named A and B . (and I have created sheets for both of them)

Now when I write something in the main sheet for customer A, the entry should automatically copied in his sheet.

And when I write something in the main sheet for customer B, the entry should automatically copied in his sheet.

macropod · #2 03-08-2014, 04:34 AM

You could add a macro like the following to your 'main' sheet's code module, but you'll need to fix your spelling before the code will work properly.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRowTgt As Long, lRowDst As Long, i As Long, j As Long
Dim StrDst As String, bFilled As Boolean, bDiff As Boolean, bCol As Boolean
With Target.Worksheet
  ' Find the last row on the input sheet
  lRowTgt = .Range("A" & .Rows.Count).End(xlUp).Row
  ' Check whether we're on the last row
  If Not Intersect(Target, .Range("A" & lRowTgt & ":D" & lRowTgt)) Is Nothing Then
    ' Check whether all cells in columns A:D on the last row are filled in
    bFilled = True
    For i = 1 To 4
      If .Cells(lRowTgt, i).Value = "" Then
        bFilled = False
      End If
    Next
    If bFilled = True Then
    ' All cells in columns A:D on the last row are filled in
      With Target.Worksheet
        ' Find the output sheet
        StrDst = "Customer " & UCase(Trim(.Cells(lRowTgt, 1).Value))
        ' Find the last row on the output sheet
        With Sheets(StrDst)
          lRowDst = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
        ' See if we can find a row on the output sheet that has the same values as the input sheet
        For i = 2 To lRowDst
          bDiff = True
          If .Cells(lRowTgt, 2).Value = Sheets(StrDst).Cells(i, 1).Value And _
            .Cells(lRowTgt, 3).Value = Sheets(StrDst).Cells(i, 2).Value And _
            .Cells(lRowTgt, 4).Value = Sheets(StrDst).Cells(i, 3).Value Then
            bDiff = False: Exit For
          End If
        Next
        If bDiff = True Then
          ' No matches, so copy from the input sheet to the output sheet
          lRowDst = lRowDst + 1
          .Range("B" & lRowTgt & ":D" & lRowTgt).Copy
          With Sheets(StrDst)
            .Paste Destination:=.Range("A" & lRowDst)
          End With
        End If
      End With
    End If
  End If
  ' Find the last row on the input sheet
  lRowTgt = .Range("F" & .Rows.Count).End(xlUp).Row
  ' Check whether we're on the last row
  If Not Intersect(Target, .Range("F" & lRowTgt & ":I" & lRowTgt)) Is Nothing Then
    ' Check whether all cells in columns F:I on the last row are filled in
    bFilled = True
    For i = 6 To 9
      If .Cells(lRowTgt, i).Value = "" Then
        bFilled = False
      End If
    Next
    If bFilled = True Then
    ' All cells in columns F:I on the last row are filled in
      With Target.Worksheet
        ' Find the output sheet
        StrDst = "Customer " & UCase(Trim(.Cells(lRowTgt, 6).Value))
        ' Find the last row on the output sheet
        With Sheets(StrDst)
          lRowDst = .Range("F" & .Rows.Count).End(xlUp).Row
        End With
        ' See if we can find a row on the output sheet that has the same values as the input sheet
        For i = 2 To lRowDst
          bDiff = True
          If .Cells(lRowTgt, 7).Value = Sheets(StrDst).Cells(i, 5).Value And _
            .Cells(lRowTgt, 8).Value = Sheets(StrDst).Cells(i, 6).Value And _
            .Cells(lRowTgt, 9).Value = Sheets(StrDst).Cells(i, 7).Value Then
            bDiff = False: Exit For
          End If
        Next
        If bDiff = True Then
          ' No matches, so copy from the input sheet to the output sheet
          lRowDst = lRowDst + 1
          .Range("G" & lRowTgt & ":I" & lRowTgt).Copy
          With Sheets(StrDst)
            .Paste Destination:=.Range("E" & lRowDst)
          End With
        End If
      End With
    End If
  End If
End With
Application.CutCopyMode = True
End Sub

nicholes · #3 03-09-2014, 12:20 AM

WOW! Thanks

but i am sorry i cannot understand what you say since i am very new and noob to excel.

however i appreciate your help. please make some formula for me if you could.

macropod · #4 03-09-2014, 12:32 AM

Quote:

Originally Posted by nicholes

please make some formula for me if you could.

It really isn't practical to do this with a formula, that's why I provided the macro. If you add it to your workbook's 'main' sheet code module, your other sheets will be updated automatically any time a new line is completed on the 'main' sheet.

To add the macro to your workbook's 'main' sheet code module, press Alt-F11 to start the VB Editor, double-click on the 'Sheet1(main)' entry on the left, then paste the code from my post into it. Oh, and fix up the spelling errors (e.g. 'Customer A', not 'cutomaer A')!

PS: Please don't quote the entire post responded to in your replies; if something needs to be quoted, quote only that part.

nicholes · #5 03-09-2014, 08:17 AM

Quote:

Originally Posted by macropod

To add the macro to your workbook's 'main' sheet code module, press Alt-F11 to start the VB Editor, double-click on the 'Sheet1(main)' entry on the left, then paste the code from my post into it. Oh, and fix up the spelling errors (e.g. 'Customer A', not 'cutomaer A')!

PS: Please don't quote the entire post responded to in your replies; if something needs to be quoted, quote only that part.

1. I have renamed the sheet Customer A.

2. Thanks for the opinion, i would now quote only important things.

Now since i am doing this for the first time, this easy job looks a bit hard for me
here is what i did>>>>>>

I copied your code, and in my workbook perss alt+ F11(it opened VB editor) i dubble click on sheet one(main) and paste your code there .

after that i go to "file" Tab and choose> "Close and return to Microsoft Excel"(alt+Q)

i dont see any changes in my workbook.nor i see any saved micro to run

what to do now?

i know i am not doing it in a right way.please help me

macropod · #6 03-09-2014, 02:10 PM

Quote:

Originally Posted by nicholes

i dont see any changes in my workbook.nor i see any saved micro to run

what to do now?

Now you enter some data on a new row the main sheet. As soon as columns A to D or F to I are completed, the data for that row will be copied to the Customer A or Customer B sheet.

The macro doesn't change any existing data. If you need to transfer the existing data, I'd suggest sorting columns A to D by customer name, transferring their data to the Customer A & Customer B sheets, then doing the same for columns F to I. You can re-sort columns A to D & F to I by date afterwards.

nicholes · #7 03-09-2014, 08:14 PM

That works fine! Thanks.

but when i save file it ask me to save with micro enable.

i save it with both file type micro enabled(xlsm) and micro free(xlsx) in both case automatically copy paste does not work when we open file after saving it.

also when i clear the content of main sheet it does not cleared from customers sheet(i want this to be done too)

i hope i am not making you angry by asking too much.
Thanks again for your kind effort.

macropod · #8 03-09-2014, 08:29 PM

If you save an Excel file in the xlsx format, the macros will be lost upon re-opening. If you saved the file in the xlsm format after adding the macro to it, the macro will still be there upon re-opening.

The 'enable macros' prompt upon opening can be avoided by storing the file in a trusted location. See File|Options|Trust Centre>Trust Centre Settings>Trusted Locations.

As for clearing, that's not what the macro does. Although it would be possible to write a macro that clears the customer sheets when the main sheet is cleared, clearing individual items would be much more difficult. As you can see, just populating the customer sheets takes quite a lot of code; working out which rows to delete would be much harder.

If you're still keen to use formulae for this, see:
http://windowssecrets.com/forums/sho...l=1#post734296
http://www.techsupportforum.com/foru...ml#post2567119
Compared to a macro-based solution, these complex formulae approaches do have the advantage of clearing the data as they're deleted from the main sheet.

nicholes · #9 03-10-2014, 04:53 AM

I need exactly what you provide me in the first link(demo.xls)

I am uploading my original file where I need to get this formula work (bigger file cannot upload in the forum so providing a link)
http://www.sendspace.com/file/wt9e6a

Please make this(demo.xls) formula work in my sheet,I would be highly obliged to you if you get my work done.

Only focus on three sheet named are> “all creadit” ,” sny” , “gul”(tab colored RED)
when i write anything in all creadit for sny, it automatically should transfer into sny sheet

and same for gul

and when i clear anything from main sheet for sny it should be removed from sny sheet

and same for gul

macropod · #10 03-10-2014, 05:44 AM

The comments in the Demo.xls file show how you can adapt the formulae to suit your needs. Since it's not that hard, I don't propose to do all the work for you. Besides, although it's possible, I really don't think it's practical to do this with a formula for more than a small amount of data - your workbook will become very slow at recalculating.

nicholes · #11 03-11-2014, 08:19 AM

Quote:

Originally Posted by macropod

The comments in the Demo.xls file show how you can adapt the formulae to suit your needs. Since it's not that hard, I don't propose to do all the work for you. Besides, although it's possible, I really don't think it's practical to do this with a formula for more than a small amount of data - your workbook will become very slow at recalculating.

i tried my best but could not make this work in my work book

i know it will become slow(my workbook) but i will remove other entries(as i dont need them) and reduce file size and make it more fast.(if anyone make that formula work in my workbook)

anyways Thanks for your kind help
I did not imagine so quick and great replies from this site or any user.

CHEERS!

macropod · #12 03-11-2014, 08:50 PM

See attached

nicholes · #13 03-12-2014, 12:20 AM

Quote:

Originally Posted by macropod

See attached

i would look into it.

but there is something i found in first look, i dont make any changes in it and why it ask to save changes everytime when i close this workbook(without making any changes in it)?

please look into it. i think i can reach to my goal from here if you just solve "save changes without making any change "

Thanks!

macropod · #14 03-12-2014, 01:05 AM

That is a function of the formulae used to do all the data lookups etc. You can't avoid that. The Demo workbook you looked at behaves the same way.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to Copy data from Outlook mail and Paste it in a Excel sheet?	padhu1989	Outlook	0	09-11-2012 04:07 AM
sheet 2 data highlight in sheet 1	gsrikanth	Excel	1	04-21-2012 06:25 PM
How to copy automatically data from Excel file to Word file?	fuchsd	Word	6	10-25-2011 05:52 AM
If two geographical data match in two sheets, copy unique id/code found in one sheet	alliage	Excel	1	09-01-2011 05:23 AM
copy cell from sheet 2 to sheet 3 macro	slipperyjim	Excel Programming	1	02-18-2010 01:31 AM