Populating companies sheets from mastersheet

[Closed]
Report
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
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 reply

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
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