PDA

View Full Version : Formating table



Duso
09-05-2010, 02:31 PM
Hi everybody
The table at the Sheet ("PSk4") consists of 3 Column groups (B3:22; E3:G22; H3:J22; ... ) . Number of 3 Column groups is changing according to the value in D17 at Sheet 2. Max value is = 50. Number of rows is changing according to the value in D20 at Sheet2 – max number = 20.
Row 25 in the midle column of each of 3 Column groups includes any positive value (C25, F25, I25, ..).
In the same way as table above, is creating table at Sheet ("PSk5“) with different data.
What I need is: I would like

1) Macro1, that creates new table at Sheet ("P1Sk4") by copying and pasting the first 2 columns from 3 Column groups from table at the Sheet ("PSk4") . "2 Column groups" are B:C; E:F; H:I; ....Paste start to A1. Max number "2 Column groups" is = 12. Next "2 Column groups" if any are, are copying to the "empty + 1" row in column A - one row after previous part of new table.
The last row, where can be copy is row 44. Columns of table can´t be split by row 44. New table must start at row 45.
Old created table must be removed. Cells to which is not copying must be empty.

2)Macro2, that creates new 2 tables at Sheets ("P2Sk4") and ("P2Sk5") acording to the compared values that are include in rows 25 in the middle column of each of "3 Column groups" tables at Sheet ("PSk4") and Sheet ("PSk5") as follows. If value in row 25 matching columns at Sheet ("P1Sk4") >= value in row 25 at Sheet ("P1Sk5"), then adequate "2 Column groups" copy to Sheet ("PSk4"), else
copy to Sheet ("PSk5"). Copy and paste is the same as by Macro1.
MS Excel 2003.
Thanks in advance for the help

RonMcK
09-05-2010, 07:38 PM
Duso,

Please upload a copy of your worksheet. This will let people here see what you are trying to accomplish. If it would help us, please include a sheet showing how the page will look after the macro has done it's work.

To upload, while you are writing your reply, scroll the page down so you see the Additional Options box and within it click the Manage Attachments button. Then, click Browse to find the file you want to upload on your computer, once you have it selected, click the Upload button to the right of your filename.

Thanks,

Duso
09-06-2010, 02:08 PM
Duso,

Please upload a copy of your worksheet. This will let people here see what you are trying to accomplish. If it would help us, please include a sheet showing how the page will look after the macro has done it's work.

To upload, while you are writing your reply, scroll the page down so you see the Additional Options box and within it click the Manage Attachments button. Then, click Browse to find the file you want to upload on your computer, once you have it selected, click the Upload button to the right of your filename.

Thanks,

I attached 2 examles to see what I want macros to do.
Sheets PSk4 and PSk5 illustrate tables how they are created by my application.
Number of "3 Columns groups" marked in Example 1 and Example2 as Grp1, 2, ... is given by cell D17 in my application at Sheet 2 (D17=48 for Example 1 and D17= 38 for Example 2). Number of rows is given in my application in cell D20 at Sheet 2 (D20=14 for Example 1 and D20= 11 for Example 2).

Macro1:
Coloured part of Row 2 of Sheet PSk4 refers to columns ("2 Column groups" ). I need Macro1 to copy columns (B3:C16, E3:F16, H3:I16, ....EM3:EN16 - Example 1 or B3:C13, E3:F13, H3:I13, ...DI3:J13 - Example 2) to Sheet P1Sk4 (A1:X58 - Example 1 or A1:X55 - Example 2) in a way such is coloured in that Sheet P1Sk4.

Example 2 illustrates condition : The last row, where can be copy is row 44. Columns of table can´t be split by row 44. New table must start at row 45.

Simply: Columns of Sheet P1Sk4 set to printing one or two pages according to the number of columns and rows of table in Sheet Psk4. Printing area for one page is A1:X44.

Macro2:

Coloured part of Row 2 of Sheets PSk4 and PSk5 referes to columns ("2 Column groups“ ). I need Macro2 to copy columns (B3:C16, E3:F16, H3:I16, ....EM3:EN16 - Example 1 or B3:C13, E3:F13, H3:I13, ...DI3:J13 - Example 2) to Sheets P2Sk4 and P2Sk5 base on value size given in coloured cells in row 25 at Sheets PSk4 and PSk5 (C3, F3, I3, ...). If value size in PSk4 >= value size in PSk5 then copy matching "2 Column groups" from Sheet PSk4 to Sheet P2Sk4, else copy matching "2 Column groups" from Sheet PSk5 to Sheet P2Sk5.

In the attached examples is value size coloured in row 25.

Green colour means that value in Sheet PSk4 >= than value in Sheet PSk5 and only green coloured columns from Sheet PSk4 may be coped to Sheet P2Sk4.

Brown colour means that value in Sheet PSk4 < than value in Sheet PSk5 and only brown coloured columns from Sheet PSk5 may be coped to Sheet P2Sk5.

Red coloured columns in Sheets PSk4 and PSk5 are not copy.

Blue coloured columns in Sheet PSk4 are paste to Sheet P2Sk4 because of equal value size in cells CL25 in both sheets.

Simply: Columns of Sheets P2Sk4 and P2Sk5 set to printing one or two pages acording to the number of columns and rows of the table in Sheet s PSk4 and PSk5 and acording to the value size of coloured cells in row 25 in Sheets PSk4 and PSk5.

Duso
09-06-2010, 02:10 PM
The second example