Populating companies sheets from mastersheet

Closed
Alin - Aug 7, 2015 at 04:12 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 11, 2015 at 12:00 PM
Hello,

Could someone help please.

I have a excel file with a master sheet which contains invoice companies. The are alphabetical ordered.

But I would like to make a connection to have each company separately and when I will add invoices in master sheet they will be added in the company sheet as well.

Please help me with this.

Cheers

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 11, 2015 at 12:00 PM
Hi Alin,

You didn't mention any specifics, so let us start with the following:
- Let's say that your master sheet has it's companies in column A (if not, then change the reference in the code found in the 2nd line).
- Each company sheet is named after it's company which is the same as column A of the master sheet (a typo means the sheet can't be found).
- The company name is the last value you enter of that specific row (because that's when the code will run).

With the above points in mind, implement the code below by right-clicking the master sheets tab and select View Code. Paste the code in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


Now back at Excel you can fill out a row on the master sheet where you enter the company name as the last value. Upon confirming the company the row will be copied and pasted to the respective company sheets first available row.

Is this to your liking?

Best regards,
Trowa
0