#1
|
|||
|
|||
Copy data
Good evening , I would like to automatically divide the data I enter into the invoice status with a macro or some other type and copy the data according to the customer into separate tabs without affecting the other sheets. I attach a model to you.
|
#2
|
|||
|
|||
Code:
Option Explicit Sub CopyOwnTab() Application.ScreenUpdating = False Dim i As Long Dim Lastrow As Long On Error GoTo M Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row Dim ans As String For i = 2 To Lastrow ans = Sheets("Master").Cells(i, 1).Value Sheets("Master").Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1) Next Application.ScreenUpdating = True Exit Sub M: MsgBox "No such sheet as " & ans & " exist" Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
I would like to push the button for example 5 hands not to carry the same line 5 times, you could set it up in my file.
I would like to transfer the data to the list, for example, I register the invoice No.1 to client A and then post the invoice No.2 to client B based on your own macro when I click on the button when I enter the second invoice to client A invoice No.1 two times that I would not want it to be. |
#4
|
|||
|
|||
Sorry ... I don't understand.
|
#5
|
|||
|
|||
I have customized your own code in my archive.
If you press twice and above the button will copy the same. I would like you not to be the one of Customer A's tariff 1 to exist once. Try my own book please |
#6
|
|||
|
|||
Press the button twice and this is what occurs :
DATE CUSTOMER INVOICE VALUE IMFORMATION 1 IMFORMATION 2 IMFORMATION 3 1/10/2019 CUSTOMER A 1 1,000.00 € AAA VVV 2/2/2019 CUSTOMER A 3 700.00 € KKK 1/10/2019 CUSTOMER A 1 1,000.00 € AAA VVV 2/2/2019 CUSTOMER A 3 700.00 € KKK Are you saying, no matter how many times you click the button, in the data always contains the same information (nothing new has been added to the LIST tab) you only want this data to show on the CUSTOMER A tab : DATE CUSTOMER INVOICE VALUE IMFORMATION 1 IMFORMATION 2 IMFORMATION 3 1/10/2019 CUSTOMER A 1 1,000.00 € AAA VVV 2/2/2019 CUSTOMER A 3 700.00 € KKK Is this correct ? |
#7
|
|||
|
|||
Αs many times as I press the button these times copy the same invoices
Ι would like to copy the new invoices only ( not old invoices) to customers' sheets. |
#8
|
|||
|
|||
Code:
Option Explicit Sub CopyOwnTab() Application.ScreenUpdating = False Dim i As Long Dim Lastrow As Long Lastrow = Sheets("LIST").Cells(Rows.Count, "B").End(xlUp).Row Dim ans As String For i = 3 To Lastrow ans = Sheets("LIST").Cells(i, 2).Value Sheets("LIST").Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1) Sheets(ans).Range("$A$1:$G$1000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes Next Application.ScreenUpdating = True End Sub |
#9
|
|||
|
|||
if I'd sum up all the values in cell D200, how could I keep that calculation after the order you gave me?
|
#10
|
|||
|
|||
Another possibility would be to use a column off to the right somewhere to keep track of what has already been copied.
This 'helper' column could be hidden if desired. Have used column J here. Code:
Sub CopyOwnTab() Dim i As Long, Lastrow As Long, ans As String Dim dest As Worksheet, recCnt As Long With Sheets("List") Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row For i = 3 To Lastrow If LCase(.Cells(i, "J").Value) <> "copied already" Then ans = .Cells(i, 2).Value On Error Resume Next 'incase the sheet does not exist Set dest = Sheets(ans) On Error GoTo 0 're-enable error notification If Not dest Is Nothing Then .Cells(i, "A").Resize(, 7).Copy dest.Range("A" & Rows.Count).End(xlUp).Offset(1) .Cells(i, "J").Value = "copied already" recCnt = recCnt + 1 Else MsgBox "Sheet " & ans & " does not exist." End If End If Set dest = Nothing Next End With MsgBox "There were " & recCnt & " records copied." End Sub |
#11
|
|||
|
|||
Good afternoon.
Additional I want for example to check the sheet customer's A and if the macro find same invoice number , do not copy this row |
#12
|
|||
|
|||
Code:
Sub CopyOwnTab_2() Dim i As Long, Lastrow As Long, ans As String Dim dest As Worksheet, recCnt As Long Dim fndRng As Range, findString As String With Sheets("List") Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row For i = 3 To Lastrow ans = .Cells(i, "B").Value 'the customer findString = .Cells(i, "C").Value 'the invoice On Error Resume Next 'incase the sheet does not exist Set dest = Sheets(ans) On Error GoTo 0 're-enable error notification If Not dest Is Nothing Then 'the customer sheet does exist 'so check if this invoice is already there Set fndRng = dest.Range("C:C").Find(What:=findString, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If fndRng Is Nothing Then 'the invoice does not exist .Cells(i, "A").Resize(, 7).Copy dest.Range("A" & Rows.Count).End(xlUp).Offset(1) recCnt = recCnt + 1 End If Else MsgBox "Sheet " & ans & " does not exist." End If Set dest = Nothing Next i End With MsgBox "There were " & recCnt & " records copied." End Sub |
#13
|
|||
|
|||
Thanks a lot!!!!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy data as well as formatting from one workbook to another. | LearnerExcel | Excel | 1 | 03-10-2018 01:35 PM |
Copy excel data | wjmj | Word | 1 | 12-18-2015 10:10 PM |
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria: | udhaya | Excel Programming | 1 | 11-12-2015 10:12 AM |
Copy Like Data | MBragg | Excel | 3 | 10-13-2015 11:45 PM |
Copy data from one form to another | jodjamz | Outlook | 0 | 04-28-2015 05:07 PM |