Copying rows automatically from one spread sheet to another when an entry is made

[mikeb7500]

I have a master payroll sheet(tab1), that when a sale is made by David, I enter David's name, date, amount of sale, commission etc. in a row. I would like to have this information(row) automatically COPY to a spreadsheet(tab2) named David automatically each time David makes a sale. The same when I enter a sale made by Mary, that when it's entered it also COPIES to a spreadsheet(tab3) named Mary...etc. What is the best way to do this...any ideas??? Thanks!

 

[KeepTrying]

Hi,

I made a sample master data sheet which looks like this:

	A	B	C	D
1	Name	Date	Amount of sale	Comission
2	David	2014.01.25	1439	4
3	John	2014.01.26	514	2
4	David	2014.01.27	1238	3
5	Mary	2014.01.28	740	2
6	Eva	2014.01.29	1453	2
7	Lara	2014.01.30	1487	3
8	John	2014.01.31	1003	4
9	David	2014.02.01	1465	4
10	Mary	2014.02.02	1167	1

<tbody>
</tbody>

Add master sheet called "MasterPayroll"+ add sheets and name them to "David" and "Mary" before run VBA code below. Code deletes cell content+ add headers to name sheets. You can change person name, header or name of master sheet as indicated by comments in code.

Sub CopyRowsForNames()


Dim Rng As Range
Dim i As Long, j As Long, Z As Long
Dim PersonName
Dim Header_


Application.ScreenUpdating = False


PersonName = Array("David", "Mary") 'you can add new names
Header_ = Array("Name", "Date", "Amount of sale", "Comission") 'Add new names for header if you want
Set Rng = ThisWorkbook.Sheets("MasterPayroll").UsedRange 'Change name of the master sheet from ""MasterPayroll"" if you want


For j = 0 To UBound(PersonName)
    Application.DisplayAlerts = False
        With Sheets(PersonName(j))
            .Cells.Clear
            .Cells(1, 1).Resize(, 4) = Header_
        End With
    Application.DisplayAlerts = True
Next j


For i = 2 To Rng.Rows.Count
    For j = 0 To UBound(PersonName)
        If Rng.Cells(i, 1) = PersonName(j) Then
            Z = Sheets(PersonName(j)).UsedRange.Rows.Count
            Rng.Rows(Rng.Cells(i, 1).Row).Copy Sheets(PersonName(j)).Cells(Z + 1, 1)
        End If
    Next j
Next i


Application.ScreenUpdating = True


End Sub

So if you enter a new name (add a new row) you can run this macro.

I hope it'll work for you.

 

[mikeb7500]

Hi,

I made a sample master data sheet which looks like this:

	A	B	C	D
1	Name	Date	Amount of sale	Comission
2	David	2014.01.25	1439	4
3	John	2014.01.26	514	2
4	David	2014.01.27	1238	3
5	Mary	2014.01.28	740	2
6	Eva	2014.01.29	1453	2
7	Lara	2014.01.30	1487	3
8	John	2014.01.31	1003	4
9	David	2014.02.01	1465	4
10	Mary	2014.02.02	1167	1

<tbody>
</tbody>

Add master sheet called "MasterPayroll"+ add sheets and name them to "David" and "Mary" before run VBA code below. Code deletes cell content+ add headers to name sheets. You can change person name, header or name of master sheet as indicated by comments in code.

Sub CopyRowsForNames()


Dim Rng As Range
Dim i As Long, j As Long, Z As Long
Dim PersonName
Dim Header_


Application.ScreenUpdating = False


PersonName = Array("David", "Mary") 'you can add new names
Header_ = Array("Name", "Date", "Amount of sale", "Comission") 'Add new names for header if you want
Set Rng = ThisWorkbook.Sheets("MasterPayroll").UsedRange 'Change name of the master sheet from ""MasterPayroll"" if you want


For j = 0 To UBound(PersonName)
    Application.DisplayAlerts = False
        With Sheets(PersonName(j))
            .Cells.Clear
            .Cells(1, 1).Resize(, 4) = Header_
        End With
    Application.DisplayAlerts = True
Next j


For i = 2 To Rng.Rows.Count
    For j = 0 To UBound(PersonName)
        If Rng.Cells(i, 1) = PersonName(j) Then
            Z = Sheets(PersonName(j)).UsedRange.Rows.Count
            Rng.Rows(Rng.Cells(i, 1).Row).Copy Sheets(PersonName(j)).Cells(Z + 1, 1)
        End If
    Next j
Next i


Application.ScreenUpdating = True


End Sub

So if you enter a new name (add a new row) you can run this macro.

I hope it'll work for you.

Thanks, KT...it looks like we're on the right track, but not quiet there. I recreated your spreadsheet, named it MasterPayroll, and added a David Tab and a Mary Tab. When I ran the Macro, the only thing that transferred to BOTH David's Tab and Mary's Tab were the Headings in Row 1:

Name

Date

Amount of sale

Comission

<tbody>
</tbody>

No Data was added. Sorry it took so long to give this a try. But still need your help...Thanks!

 

[mikeb7500]

Thanks, KT...it looks like we're on the right track, but not quiet there. I recreated your spreadsheet, named it MasterPayroll, and added a David Tab and a Mary Tab. When I ran the Macro, the only thing that transferred to BOTH David's Tab and Mary's Tab were the Headings in Row 1:

Name

Date

Amount of sale

Comission

<tbody>
</tbody>

No Data was added. Sorry it took so long to give this a try. But still need your help...Thanks!

 

[KeepTrying]

If names are the same in column A are called the same as sheet names ("David", "Mary") then code should work. It's not so easy to figure out why code is not working for you without the workbook. Here I changed code a little bit, you can see update in blue color.

Sub CopyRowsForNames2()


Dim Rng As Range
Dim i As Long, j As Long, Z As Long
Dim PersonName
Dim Header_


Application.ScreenUpdating = False


PersonName = Array("David", "Mary") 'you can add new names
Header_ = Array("Name", "Date", "Amount of sale", "Comission") 'Add new names for header if you want
Set Rng = ThisWorkbook.Sheets("MasterPayroll").UsedRange 'Change name of the master sheet from ""MasterPayroll"" if you want


For j = 0 To UBound(PersonName)
    Application.DisplayAlerts = False
        With Sheets(PersonName(j))
            .Cells.Clear
            .Cells(1, 1).Resize(, 4) = Header_
        End With
    Application.DisplayAlerts = True
Next j


For i = 2 To Rng.Rows.Count
    For j = 0 To UBound(PersonName)
        If Rng.Cells(i, 1) = PersonName(j) Then
            Z = Sheets(PersonName(j)).UsedRange.Rows.Count
            Sheets(PersonName(j)).Range(Cells(Z + 1, 1).Address, Cells(Z + 1, 4).Address) = _
            Rng.Range(Cells(i, 1).Address, Cells(i, 4).Address).Value
        End If
    Next j
Next i


Application.ScreenUpdating = True


End Sub

 

[mikeb7500]

If names are the same in column A are called the same as sheet names ("David", "Mary") then code should work. It's not so easy to figure out why code is not working for you without the workbook. Here I changed code a little bit, you can see update in blue color.

Sub CopyRowsForNames2()


Dim Rng As Range
Dim i As Long, j As Long, Z As Long
Dim PersonName
Dim Header_


Application.ScreenUpdating = False


PersonName = Array("David", "Mary") 'you can add new names
Header_ = Array("Name", "Date", "Amount of sale", "Comission") 'Add new names for header if you want
Set Rng = ThisWorkbook.Sheets("MasterPayroll").UsedRange 'Change name of the master sheet from ""MasterPayroll"" if you want


For j = 0 To UBound(PersonName)
    Application.DisplayAlerts = False
        With Sheets(PersonName(j))
            .Cells.Clear
            .Cells(1, 1).Resize(, 4) = Header_
        End With
    Application.DisplayAlerts = True
Next j


For i = 2 To Rng.Rows.Count
    For j = 0 To UBound(PersonName)
        If Rng.Cells(i, 1) = PersonName(j) Then
            Z = Sheets(PersonName(j)).UsedRange.Rows.Count
            Sheets(PersonName(j)).Range(Cells(Z + 1, 1).Address, Cells(Z + 1, 4).Address) = _
            Rng.Range(Cells(i, 1).Address, Cells(i, 4).Address).Value
        End If
    Next j
Next i


Application.ScreenUpdating = True


End Sub

KT, I haven't applied this code to my spreadsheet yet, I RECREATED YOUR SPREADSHEET, to see it work. (FYI, I have DAVID in cell B3 and Mary in cell B6). I copied and pasted the revised formula and unfortunately I have the SAME results. The only info that transfers is the same:

Name

Date

Amount of sale

Comission

<tbody>
</tbody>

Please check it on yours, because I copied your spreadsheet EXACTLY, as well as copying and pasting your code EXACTLY. PLEASE don't give up on me, we are SO close. Thanks

 

[KeepTrying]

Ok, I think I found root causes:
- it makes sense if you use "David" or "DAVID". Now I updated my code.
- it makes huge sense that your Name column in column B and not in A (as you originally showed on sample screen)

See revised code below. If column A is empty on sheet "MasterPayroll": no change is necessary. If you have data in column A on sheet "MasterPayroll": update this line as comment says:

Const ColumnID = 1 'if you have data in column "A" on sheet "MasterPayroll": change 1 to 2

Sub CopyRowsForNames3()


Dim Rng As Range
Dim i As Long, j As Long, Z As Long
Dim PersonName
Dim Header_
Const ColumnID = 1 'if you have data in column "A" on sheet "MasterPayroll": change 1 to 2


Application.ScreenUpdating = False


PersonName = Array("DAVID", "Mary") 'you can add new names
Header_ = Array("Name", "Date", "Amount of sale", "Comission") 'Add new names for header if you want
Set Rng = ThisWorkbook.Sheets("MasterPayroll").UsedRange 'Change name of the master sheet from ""MasterPayroll"" if you want


For j = 0 To UBound(PersonName)
    Application.DisplayAlerts = False
        With Sheets(PersonName(j))
            .Cells.Clear
            .Cells(1, 2).Resize(, 4) = Header_
        End With
    Application.DisplayAlerts = True
Next j


For i = 2 To Rng.Rows.Count
    For j = 0 To UBound(PersonName)
        If Rng.Cells(i, ColumnID) = PersonName(j) Then
            Z = Sheets(PersonName(j)).UsedRange.Rows.Count
            Sheets(PersonName(j)).UsedRange.Range(Cells(Z + 1, 1).Address, Cells(Z + 1, 4).Address) = _
            Rng.Range(Cells(i, ColumnID).Address, Cells(i, ColumnID + 3).Address).Value
        End If
    Next j
Next i


Application.ScreenUpdating = True


End Sub

 

[mikeb7500]

KT, I haven't applied this code to my spreadsheet yet, I RECREATED YOUR SPREADSHEET, to see it work. (FYI, I have DAVID in cell B3 and Mary in cell B6). I copied and pasted the revised formula and unfortunately I have the SAME results. The only info that transfers is the same:

Name

Date

Amount of sale

Comission

<tbody>
</tbody>

Please check it on yours, because I copied your spreadsheet EXACTLY, as well as copying and pasting your code EXACTLY. PLEASE don't give up on me, we are SO close. Thanks

KT, IT WORKS! Please ignore my last post, When I started, I actually typed rows 1,2,3, etc and columns a,b,c etc...I deleted those and re positioned David in cell A2 and Mary in B5. I'll let you know how it works in MY spreadsheet. Thanks!

 

[KeepTrying]

Great news, thanks. You can use my latest code on your original layout (as you wrote, names are in column B on sheet "MasterPayroll"). Please let me know if it works.

 

[mikeb7500]

Great news, thanks. You can use my latest code on your original layout (as you wrote, names are in column B on sheet "MasterPayroll"). Please let me know if it works.

I will KT. I do have a question though, when I add a NEW NAME to the list, with a corresponding TAB, it appears I will have to enter the name in the code manually, or will it do it automatically? If I have to enter it manually, is there a way to get it to work from a Drop Down List of Names. So as I add a new name (Mark) to the drop down list, and I add a (Mark) Tab, that it will work? Just wondering, if this is possible...that is what I'm trying to achieve...thanks I can get a copy of my spreadsheet if you would like to take a look at it...let me know. Thanks!