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]

Both way is possible:
1. As I wrote in my code: you can add new names to this array:

PersonName = Array("DAVID", "Mary") 'you can add new names

At the moment you need to update list above+ add new sheet manually but I can update macro that new names will be added automatically.

2. Drop down list is also possible. I'd use data source for drop-down list and macro would go through this list and add personal sheets.

Think which solution would be better for you. Then if you could upload a sample spreadsheet it'd be great.

Thanks.

 

[mikeb7500]

Both way is possible:
1. As I wrote in my code: you can add new names to this array:

PersonName = Array("DAVID", "Mary") 'you can add new names

At the moment you need to update list above+ add new sheet manually but I can update macro that new names will be added automatically.

2. Drop down list is also possible. I'd use data source for drop-down list and macro would go through this list and add personal sheets.

Think which solution would be better for you. Then if you could upload a sample spreadsheet it'd be great.

Thanks.

KT, I would like to send you a copy of the Workbook I'm applying this code to, but don't see a way to attach it to this Post. Please advise on how I could send you a copy of my spreadsheet for your review. The Workbook consists of a Master Payroll Spreadsheet, a Data Sheet, then the individual Rep sheets (David Tab, Mary Tab, etc). The friend I am developing this for(for free), has a lot of turnover in his business. So I would prefer that when I would add a new rep's name to the Data Sheet, so it appears on the Drop-Down List(this is done). Once the name is added to the Drop-Down List, the code would then add the new name automatically, so that all I have to do is add a Tab and name it the new name, and it will populate automatically. Please advise the best way to bet you my worksheet. Thanks again!

 

[KeepTrying]

As far as I know You cannot upload file directly to the forum only to a file sharing site (like Drop box, Google Drive etc) and copy URL of the file to here.

 

[mikeb7500]

As far as I know You cannot upload file directly to the forum only to a file sharing site (like Drop box, Google Drive etc) and copy URL of the file to here.

Here's a link to DropBox. Thanks again!

https://www.dropbox.com/s/rohm9znozcg95pw/2015 Master Commission Pay 012615.xlsm?dl=0

 

[KeepTrying]

Hi,

Layout of your sample spreadsheet is quite different again. My comments for revised code below:
- I used the source of data validation on sheet "YTD Pay", column F: this source range is in column C on sheet "Data Page"
- code adds missing sheets for names (no need to add name sheets manually). You need to add new names to column A on sheet "Data Page" then column C will update automatically with names due to your formula

Sub CopyRowsForNames4_4()


Dim Rng As Range
Dim D As Object
Dim Lap As Worksheet
Dim Van() As Boolean
Dim i As Long, j As Long, Z As Long
Dim X


Application.ScreenUpdating = False


Set Rng = ThisWorkbook.Sheets("YTD Pay").UsedRange 'Change name of the master sheet from "YTD Pay" and rename sheet in workbook if you want
Set D = CreateObject("Scripting.Dictionary")


'Read name sheets into array based on source of Data Validation list on sheet "Data Page" in column C:
For i = 2 To Sheets("Data Page").Range("c2").CurrentRegion.Rows.Count
    If Sheets("Data Page").Cells(i, 3).Value <> "" Then D(CStr(Sheets("Data Page").Cells(i, 3).Value)) = ""
Next i
'-----------------------------------------------------------------


'add sheets based on name array above+ delete cells on existing name sheets:
ReDim Van(0 To D.Count - 1)
X = D.keys


For Each Lap In Sheets
    For i = 0 To D.Count - 1
        If Lap.Name = X(i) Then
            Van(i) = True
            Exit For
        End If
    Next i
Next Lap
For i = 0 To D.Count - 1
    If Not Van(i) Then
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = X(i)
    Else
        Sheets(X(i)).Cells.Clear
    End If
Next i
'-----------------------------------------


' add header to name sheets
With Sheets("YTD Pay")
    .Select
    .Range(Cells(8, 1), Cells(9, Rng.Columns.Count)).Copy
End With


For i = 0 To D.Count - 1
    With Sheets(X(i))
        .Select
        .Cells(1, 1).Select
        ActiveSheet.Paste
        .UsedRange.Columns.AutoFit
    End With
Next i
Application.CutCopyMode = False


'-----------------------------------------------------------------


'Copy rows (with appropriate names) from sheet "YTD Pay" to name sheets:
For i = 10 To Rng.Rows.Count
    For j = 0 To D.Count - 1
        If Sheets("YTD Pay").Cells(i, 6).Value = X(j) Then
            Z = Sheets(X(j)).UsedRange.Rows.Count
            Sheets(X(j)).Range(Cells(Z + 1, 1).Address, Cells(Z + 1, Rng.Columns.Count).Address) = _
            Sheets("YTD Pay").Range(Cells(Sheets("YTD Pay").Cells(i, 6).Row, 1).Address, Cells(Sheets("YTD Pay").Cells(i, 6).Row, Rng.Columns.Count).Address).Value
        End If
    Next j
Next i


Sheets("YTD Pay").Select


Application.ScreenUpdating = True


End Sub

 

[mikeb7500]

KT, when I add a name, it adds a new named sheet...that's great! Each of the sheets have the names of each column as on Master Payroll Sheet, but the data doesn't populate. I get a message that says "can't execute code in break mode." We're almost there! Thanks for all your help!

 

[KeepTrying]

Code is working for me without any issue.


There can be different reasons for this error:
- Do you have a break point in your code before run it? If yes, remove it.
- A UDF or ActiveX control can also ruin your code
- check if another macro is in break mode and if yes, press stop button in VBE
- Do you have any Worksheet_Change or Worksheet_Activate subroutines in your file?
- etc.


Where does macro stop when you get this error message? In VBE referring line should be highlighted where the error occures. Please copy that line here.


Or you can upload your updated spreadsheet again and I can check.

 

[mikeb7500]

Code is working for me without any issue.


There can be different reasons for this error:
- Do you have a break point in your code before run it? If yes, remove it.
- A UDF or ActiveX control can also ruin your code
- check if another macro is in break mode and if yes, press stop button in VBE
- Do you have any Worksheet_Change or Worksheet_Activate subroutines in your file?
- etc.


Where does macro stop when you get this error message? In VBE referring line should be highlighted where the error occures. Please copy that line here.


Or you can upload your updated spreadsheet again and I can check.

Please check it out...thanks!

https://www.dropbox.com/s/xy4hbgrpbh214rk/2015 Master Commission Pay Sheets Added.xlsm?dl=0

 

[KeepTrying]

Hi,

I saw you added my code to sheet "YTD Pay" in VBE like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sub CopyRowsForNames4_4()

This is not correct as you cannot use two "Sub". I'd suggest to delete code for sheet "YTD Pay" in VBE and insert a module and put my latest code to it from my referring post. Short instructions:
- select "Insert" Menu in VBE
- click on "Module"
- double click on module called "Module1" on the left side
- copy and paste my code to there
- press F5 in VBE to run code or: go back to excel and press ALT+F8. From drop down list select "This Workbook" then click on the name of the code, finally hit "Run" button.