#1
|
|||
|
|||
How to copy data automatically to particular sheet?
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. |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. Last edited by macropod; 03-09-2014 at 12:23 AM. Reason: Deleted unnecessary quote of entire post replied to |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Quote:
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 |
#6
|
||||
|
||||
Quote:
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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 |
#10
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Quote:
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! |
#12
|
||||
|
||||
See attached
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
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! |
#14
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |