Macro - Insert a new row in a specific place of a diff sheet

November 11, 2016 at 08:43:01
Specs: Windows 7
Hi everyone.

I am having a problem and for ease of reading, I will expose my problem by topics. The first three topics explain how the worksheet is organized and the others what I want to do.

i) Basically, I have like a "main" sheet with all staff expenses and it is organized in 7 columns and around 2000 rows. The columns are: "staff" (with the staff names like "Michael"), "Expense code" (like "S017P-Taxi"), "Staff RC" (like "M3310"), "BRC" (like "4111"), "Description" (like "Professor"), "Expense date" (like "16-03-2015") and "Gross Expense" (like "400").

ii) This "main" sheet is also organized by groups and sub-groups. There are two hierarchy levels for groups. For instance imagine that "Michael" have more than one expense code, so "Michael" is a group with many expense codes inside like "S017P - Taxi", "S019P - Meals" and "S012P - Other transports". Now each sub-group can have more than one line associated with, and these lines are filled with the respective information (see next schema).


Stafff | Expense code | Staff RC | BRC | Description | Expense date | Gross expense

Michael
______ S017P-Taxi
__________________M3310____4111____Professor_____16-03-2015 __440
__________________M3310____4111____Professor _____16-07-2015 __450
______S019P-Meals
__________________M3310____4111____Professor_____16-03-2015 ___24
__________________M3310____4111____Professor _____28-03-2015 ___18

Sarah
______S017P-Taxi
__________________M3324____4156____Student ______16-03-2015 ___200
__________________M3324____4156____Student ______16-07-2015 ___450

iii) In the previous example "Michael" and "Sarah" are first level groups and "S017P-Taxi" and "S019P-Meals" second level groups. Besides this "main" sheet I have another sheet, also organized hierarchically by groups, but with more sub-levels like it will be illustrated. The information on the two sheets are the same but are differently organized. The second sheet is presented on the next schema.

Year|Month & Year|Expense Code|Staff|Staff RC|BRC| Description|Exp. date|GrossExp

2015
_____March 2015
__________________S017P-Taxi
_____________________________Michael
___________________________________M3310_4111_Professor_16-03-2015_440
_____________________________Sarah
___________________________________M3324_4156_Student _16-03-2015_200
__________________S019P-Meals
_____________________________Michael
__________________________________ M3310_4111_Professor_16-03-2015__24
__________________________________ M3310_4111_Professor_28-03-2015__18
_____July 2015
__________________S017P-Taxi
_____________________________Michael
__________________________________M3310_4111_Professor_16-07-2015_450
__________________S017P-Taxi
_____________________________Sarah
__________________________________M3324_4156_Student_16-07-2015__450

iv) So, when adding some new line with some new information at "main" sheet I want that this new info will be automatically added to the second sheet and it must be correctly organized by date. The new row must be placed on the correct subgroup of the second sheet, which means that it must correspond to a specific year, month, expense code and person. Each new row must be organized by date. Note that I have information for all months of 2014, 2015 and 2016.

Do you have any idea how can I do this? I think that I will need to use Macros to make this thing work. If you need further information please ask.

Thank you very much for your time and sorry for the length of this post.

message edited by Fredericoelho


See More: Macro - Insert a new row in a specific place of a diff sheet

Report •

#1
November 11, 2016 at 09:52:23
I am no expert on Pivot Tables, but I think that they can be used to what you want,

Do a Google search for Pivot Tables, review some of the links and let us know if they will work for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 14, 2016 at 03:56:15
Hi DerbyDad03, and thank you for your time. Pivot tables cannot be uses here because there are empty cells on both sheets. I am not an expert in VBA but I think that the only way to do this is with macros. =/

Report •
Related Solutions


Ask Question