0
Thanks

A few words of thanks would be greatly appreciated.

Populate Multiple Sheets Based on Data Entered in a Main Excel Sheet

This article will explain how you populate multiple excel sheets based on data entered in another main sheet.



Example

I'm making an accounting workbook with the following criteria:

In the first sheet called "Account", all the transactions will be listed on table that goes from C6-H6, and on down for every entry. One of the columns in the table is labeled "Sector" and refers to expenses or revenues sources. There are 7 sectors and they each have a sheet in this workbook.

What I would like to do, is have each individual sector's sheet automatically populate with data each time the Account" sheet is updated, and have it based on the Sector column in the table. So basically, if the sector says "General", I would like for the entire row to be transferred to the sheet called "General". And so on for each individual sector.

Solution

Note 1: The Sector is entered in column H.

Note 2: Enter the sector as the last value of the row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets(Target.Value).Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


To implement the code:
  • Right-click on the "Account" tab and select View code.
  • Paste the code in the big white field.


Thanks to TrowaD for this tip.
Photo: Unsplash
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Populate Multiple Sheets Based on Data Entered in a Main Excel Sheet », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!