Automatically copy and insert data from one spreadsheet to another.

[Anonymous57]

Currently I have a spreadsheet with 27 tabs. The first is a master transactions list, and the other 26 are pages of transactions that relate to individual clients. When I enter transaction data (a row with 5 columns) onto the master list, I want it to automatically insert a row on the appropriate client tab at the bottom of the list of transactions, and paste the transaction row just added to the master list.

To be clear, what I want to do is:
1) Insert a row onto my master transaction list
2) copy the data into that row
and then I want the spreadsheet to automatically copy the row I just created/entered data into and insert it at the bottom of the appropriate client's transaction list.

I can't seem to find a solution anywhere.

 

[cb12]

a complicated way to do it might be like this:

Sub Activate_Data_Copy()
        Application.OnKey "~", "Copy_Data"
End Sub

Then have a procedure titled Copy_Data where you use a select Case to determine which sheet to copy to. Example being

Sub Copy_Data()
Dim Customer As String

Select Case Customer
    Case "Dave"
        ActiveCell.EntireRow.Copy
        Sheets("Dave").Select
        ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteValues
        ActiveCell.Offset(1, 0).Select
        Sheets("Master").Activate
        ActiveCell.Offset(1, 0).Select
    
    Case "Steve"
        ActiveCell.EntireRow.Copy
        Sheets("Steve").Select
        ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteValues
        ActiveCell.Offset(1, 0).Select
        Sheets("Master").Activate
        ActiveCell.Offset(1, 0).Select
    End Select
End Sub

Haven't tested this to see if it works. I think you'd be much better off just copying the rows in bulk once you had finished, personally.

Sample code for this might be

Sub Copy_Data()
Dim FilterCriteria
    Sheets("Master").Activate
        Range("A1:AS30000").Select
    Selection.AutoFilter
        FilterCriteria = "John"
            Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Copy
            Sheets("John").Select
                Range("A1").Select
            ActiveSheet.Paste
    Application.CutCopyMode = False
        Sheets(1).Activate
            Selection.AutoFilter field:=1
                Selection.AutoFilter
                    Range("A1").Select
End Sub

All just ideas, probably far simpler ways of accomplishing both.

 

[Anonymous57]

The simpler solution works great, except for a couple of thiings:

1) It filters/hides everything except "john" on the "master" tab
2) It overwrites everything on the "John" tab. At the bottom of the transaction list, I want a totals row that sums each column. Is there a way to allow this? Maybe by inserting john's transactions instead of copying?

To expand:

3) How do I add more people? Like, what if there are "john", "steve", and "bob".
4) Also, what if the filter for John can be either "john" or "JKD"

Thank you very much for your help!

 

[cb12]

I suspect issue 1 was my careless use of "sheets(1)" where your "master" sheet isn't the first. If the code is executing properly it should unfilter the results.

As for the second issue, I'm self taught with VBA and have only been going for about 6 months so I don't know everything. I'm not sure you can avoid (with this code) it pasting over the entire sheet. The easiest solution under that assumption would be to incorporate the totals in the macro. Here is another example

Sub Copy_Data()
Dim FilterCriteria
Dim numrows As Integer, myRange As Range

    Sheets("Master").Activate
        Range("A1:AS30000").Select
    Selection.AutoFilter
        FilterCriteria = "John"
            Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Copy
            Sheets("John").Select
                Range("A1").Select
            ActiveSheet.Paste
    Application.CutCopyMode = False

    Set myRange = Columns("A:A")
    numrows = Application.WorksheetFunction.CountA(myRange)
        Cells(Rows.Count, 1).End(xlUp).Select
            Range("B" & numrows + 1) = WorksheetFunction.Sum(Range("B1:B" & numrows))
            Range("C" & numrows + 1) = WorksheetFunction.Sum(Range("C1:C" & numrows))
        
    Sheets("John").Activate
        Selection.AutoFilter field:=1
            Selection.AutoFilter
                Range("A1").Select
          
End Sub

Hope this is a workable solution

 

[cb12]

Didn't see the second reply.

This is where a different solution might be better. To add other names you would need to simply c+p the code below whats already there and replace all references to John with the relevant name. The same goes for however many rows and indeed which you want to get totals for. As for the John/JKD issue perhaps run this macro first - or incorporate it into any other.

Sub NameReplacer()
    Cells.Replace What:="JKD", Replacement:="John", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Again simply c+p and replace JKD and John for the relevant info.

HTH

 

[Anonymous57]

Here is the actual code I ended up putting together based on what you said. I added a second criteria connected by an "or" and that works exactly the way I wanted to for adding all of john's two accounts to one page. Now the problem is, I can't add a third+ criteria. Is there a way to do that?

Also, I want the copying to happen based on the account title in column B of the "master" list, but copy the entire row a until the end. Right now it only copies column b until the end.

The last thing (that would make the code perfect) is if after all the rows are copied, it could insert a row before the total row, and make sure the total row is the same formatting as all the other rows.

Again, thank you for your help...I'm so close to a perfect solution I can taste it! lol

Sub Copy_Data()

Dim FilterCriteria
Dim numrows As Integer, myRange As Range

    Sheets("Master").Activate
        Range("B1:BS30000").Select
    Selection.AutoFilter
        FilterCriteria = "John Account1"
            Selection.AutoFilter Field:=1, Criteria1:=FilterCriteria, Operator:=xlOr, _
           Criteria2:="John Account2"
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Copy
            Sheets("John").Select
                Range("A1").Select
            ActiveSheet.Paste
    Application.CutCopyMode = False

    Set myRange = Columns("A:A")
    numrows = Application.WorksheetFunction.CountA(myRange)
        Cells(Rows.Count, 1).End(xlUp).Select
            Range("B" & numrows + 1) = WorksheetFunction.Sum(Range("B1:B" & numrows))
            Range("D" & numrows + 1) = WorksheetFunction.Sum(Range("D1:D" & numrows))
        
    Sheets("Master").Activate
        Selection.AutoFilter Field:=1
            Selection.AutoFilter
                Range("A1").Select
    
    
    Sheets("Master").Activate
        Range("B1:BS30000").Select
    Selection.AutoFilter
        FilterCriteria = "Steve Account1"
            Selection.AutoFilter Field:=1, Criteria1:=FilterCriteria
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Copy
            Sheets("Steve").Select
                Range("A1").Select
            ActiveSheet.Paste

    Application.CutCopyMode = False

    Set myRange = Columns("A:A")
    numrows = Application.WorksheetFunction.CountA(myRange)
        Cells(Rows.Count, 1).End(xlUp).Select
            Range("B" & numrows + 1) = WorksheetFunction.Sum(Range("B1:B" & numrows))
            Range("D" & numrows + 1) = WorksheetFunction.Sum(Range("D1:D" & numrows))
        
    Sheets("Master").Activate
        Selection.AutoFilter Field:=1
            Selection.AutoFilter
                Range("A1").Select
          
End Sub