Linked Tab Macro

[ebisabes]

how do I code a macro to insert 1 row (in correct order) into one tab ("Daily") whenever a set of rows (7 rows) are inserted into a liked tab ("Weekly")?????

 

[SkipVought]

Hi,

Please don't tell me that you have a tab for every day of the week and every week of the year. That is almost ALWAYS a HUGE mistake committed by novice users, who STORE their data like they want to SEE the data reported. ALL you data sould be in a SINGLE, well designed table.

Insert a row? I AVOID Insert as much as possible. Find the BOTTOM of your table, add the data and SORT into order.



Skip,

_[red][/red]

 

[ebisabes]

no, I don't have a tab for every day or week. I used those titles as an example. I have one tab for the daily spread of hours worked by employees, each employee name is in a cell in the A column & there are 7 rows for each employee & they are in alphabetical order going down. All the way to the right, there is a sum of the daily spread in each of the 7 rows for each person (1 per differnt type of hours worked)

when i add a new employee on the daily spread tab I also need it added in the same alpha order on the summary tab where each employee only has 1 row to their name & the 7 sums are spread out throughout their 1 row.

I just want to know if I can make it so that when I insert a new set of 7 rows for a new employee, it will insert 1 row in that order in the linked tab.

thank you skip;-)

 

[SkipVought]

Sure would be simpler from a functional standpoint if each unique record (employee) were one row in a TABLE.

Then the SUMMARY is a fairly simple process.

You really complicate (requiring programming, usually) if you wrap the data.

Addin a new row in a table is SIMPLE to process. Your approch is COMPLEX.

Skip,

_[red][/red]

 

[ebisabes]

I asked you if this was possible with a Macro in the Microsoft office forum & you said "YES" & to post my question here.

does this mean NO? or you don't know?

 

[SkipVought]

I'm saying that there may be a better way to structure your worksheet.

So do you want to insert ONE row or SEVEN rows?



Skip,

_[red][/red]

 

[CBasicAsslember]

I am having a hard time understanding your spread sheet layout. If I may make a suggestion. Use the macro recorder and record what you want the VBA code to do. Post the code here so we can get a better idea of what your trying to do.

 

[SkipVought]

One suggestion, using my "AVOIDING INSERT" tip, add 2 new columns: the first is the primary key value (a distinct value for each employee) then second is the secondary key value (1 to 7)

The the macro adds the key set for a given employee. The primary key value could be, for instance, Last & First. Then getting values into the order you want is a 2-key sort.

Need more info on how you are mapping the data to the summary sheet. Single rows, as originally suggested would summarize easily using the PivotTable wizard or even the SUBTOTAL feature.

Skip,

_[red][/red]

 

[Fenrirshowl]

If I understand it, the sheet with the sets of 7 rows are in order already (?)

If so, given that you will be able to identify the first row of data of the first record you will be able to know the first row of data for every other record (i.e. If starting on row 2 and there are no blank rows, the next record would start on row 9 and so on).

If this is right, you can set up your summary table using index functions or offsets based on these known cell references. Then, when another set of rows are added, you need only copy down the formulae on your summary sheet by one row to update the table. Everything else should fall through.

If you wanted this to be automated, I would consider the Worksheet Activate event and include a reference to two CountA functions (one for each sheet) to see if your summary table is the correct length. I.e. if integer part of CountA of column A of your 7 row data sheet divided by 7 (to allow for Title rows etc) is not equal to the number of rows in your summary table (another CountA on the appropriate range), copy the formula down or delete it from the appropriate number of cells.