Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-23-2019, 07:16 AM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default 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.
Attached Files
File Type: xlsx EXAMPLE.xlsx (11.2 KB, 11 views)
Reply With Quote
  #2  
Old 02-23-2019, 10:06 AM
Logit Logit is offline Copy data Windows 10 Copy data Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Attached Files
File Type: xlsm Copy Col A to Own Sheets.xlsm (21.7 KB, 9 views)
Reply With Quote
  #3  
Old 02-24-2019, 05:40 AM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 02-24-2019, 08:18 AM
Logit Logit is offline Copy data Windows 10 Copy data Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Sorry ... I don't understand.
Reply With Quote
  #5  
Old 02-24-2019, 08:48 AM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

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
Attached Files
File Type: xlsm EXAMPLE.xlsm (21.2 KB, 10 views)
Reply With Quote
  #6  
Old 02-24-2019, 09:01 AM
Logit Logit is offline Copy data Windows 10 Copy data Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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 ?
Reply With Quote
  #7  
Old 02-24-2019, 09:15 AM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

Α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.
Reply With Quote
  #8  
Old 02-24-2019, 11:56 AM
Logit Logit is offline Copy data Windows 10 Copy data Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Reply With Quote
  #9  
Old 02-24-2019, 02:18 PM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

if I'd sum up all the values in cell D200, how could I keep that calculation after the order you gave me?
Reply With Quote
  #10  
Old 02-24-2019, 04:16 PM
NoSparks NoSparks is offline Copy data Windows 7 64bit Copy data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #11  
Old 02-25-2019, 09:48 AM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 02-25-2019, 12:46 PM
NoSparks NoSparks is offline Copy data Windows 7 64bit Copy data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #13  
Old 02-25-2019, 01:32 PM
DIMI DIMI is offline Copy data Windows 7 32bit Copy data Office 2007
Advanced Beginner
Copy data
 
Join Date: Aug 2017
Posts: 37
DIMI is on a distinguished road
Default

Thanks a lot!!!!
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft