VBA help

[Isabella]

Hi All, i have a work task which i perform every month which i need automating. I have 3 worksheets at present (RawData, SMPJPD15, SMPUB915). From the raw data i will copy data for each unique security (Col B) to its own worksheet and then insert row between each unique portfolio. See below examples:

Ranges are correct as shown in the examples.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
4	PfCode	Security	Short Name	ReportGroup	Category&Subtype	IDNumber	TransShortName	BrokerCode	ContractDate	SettlementDate	TransUnits
5	RSTAHY	SMPJPD15	SMP JP 061215 CC	85	DS04	157957	DS-PUR	JPMAUD	22/12/2010	22/12/2010	-3,500,000.00
6	RSTAHY	SMPJPD15	SMP JP 061215 CC	85	DS04	158305	DS-PUR	JPMAUD	23/12/2010	23/12/2010	-440,000.00
7	RSTBFA	SMPJPD15	SMP JP 061215 CC	85	DS04	157959	DS-PUR	JPMAUD	22/12/2010	22/12/2010	-3,150,000.00
8	RSTBFA	SMPJPD15	SMP JP 061215 CC	85	DS04	158304	DS-PUR	JPMAUD	23/12/2010	23/12/2010	-480,000.00
9	RTFDBG	SMPJPD15	SMP JP 061215 CC	85	DS04	157958	DS-PUR	JPMAUD	22/12/2010	22/12/2010	-770,000.00
10	RTFDBG	SMPJPD15	SMP JP 061215 CC	85	DS04	160168	DS-PUR	JPMSYD	30/12/2010	30/12/2010	-280,000.00
11	RTFDBG	SMPJPD15	SMP JP 061215 CC	85	DS04	160457	INT	INT	31/12/2010	31/12/2010	0.00
12	RTEYFG	SMPUB915	SMP UBS 092015 CC	85	DS04	150708	DS-PUR	UBSAUD	3/12/2010	3/12/2010	-380,000.00
13	RTEYFG	SMPUB915	SMP UBS 092015 CC	85	DS04	151121	DS-PUR	UBSAUD	6/12/2010	6/12/2010	-440,000.00
14	RTEYFG	SMPUB915	SMP UBS 092015 CC	85	DS04	152649	DS-PUR	UBSAUD	8/12/2010	8/12/2010	-520,000.00
15	RTFDBG	SMPUB915	SMP UBS 092015 CC	85	DS04	154816	DS-PUR	UBSAUD	14/12/2010	14/12/2010	-260,000.00
16	RTFDBG	SMPUB915	SMP UBS 092015 CC	85	DS04	155882	DS-PUR	UBSAUD	16/12/2010	16/12/2010	-570,000.00
17	RTFDBG	SMPUB915	SMP UBS 092015 CC	85	DS04	158291	DS-PUR	UBSAUD	23/12/2010	23/12/2010	-490,000.00
18	RTFDBG	SMPUB915	SMP UBS 092015 CC	85	DS04	159263	DS-PUR	UBSAUD	29/12/2010	29/12/2010	-520,000.00
19	RSTBFA	SMPUB915	SMP UBS 092015 CC	85	DS04	160460	INT	INT	31/12/2010	31/12/2010	0.00
20	RSTBFA	SMPUB915	SMP UBS 092015 CC	85	DS04	160656	DS-PUR	UBSAUD	31/12/2010	31/12/2010	-320,000.00
RawData

Excel Workbook
	A	B	C	D	E	F	G	H	I
5	PfCode	Security	Short Name	IDNumber	TransShortName	BrokerCode	Contract Date	SettlementDate	Trans Units
6	RSTAHY	SMPJPD15	SMP JP 061215 CC	157957	DS-PUR	JPMAUD	22/12/2010	22/12/2010	-3,500,000.00
7	RSTAHY	SMPJPD15	SMP JP 061215 CC	158305	DS-PUR	JPMAUD	23/12/2010	23/12/2010	-440,000.00
8
9	RSTBFA	SMPJPD15	SMP JP 061215 CC	157959	DS-PUR	JPMAUD	22/12/2010	22/12/2010	-3,150,000.00
10	RSTBFA	SMPJPD15	SMP JP 061215 CC	158304	DS-PUR	JPMAUD	23/12/2010	23/12/2010	-480,000.00
11
12	RTFDBG	SMPJPD15	SMP JP 061215 CC	157958	DS-PUR	JPMAUD	22/12/2010	22/12/2010	-770,000.00
13	RTFDBG	SMPJPD15	SMP JP 061215 CC	160168	DS-PUR	JPMSYD	30/12/2010	30/12/2010	-280,000.00
14	RTFDBG	SMPJPD15	SMP JP 061215 CC	160457	INT	INT	31/12/2010	31/12/2010	0.00
15
16
SMPJPD15

Excel Workbook
	A	B	C	D	E	F	G	H	I
5	PfCode	Security	Short Name	IDNumber	TransShortName	BrokerCode	Contract Date	SettlementDate	Trans Units
6	RTEYFG	SMPUB915	SMP UBS 092015 CC	150708	DS-PUR	UBSAUD	3/12/2010	3/12/2010	-380,000.00
7	RTEYFG	SMPUB915	SMP UBS 092015 CC	151121	DS-PUR	UBSAUD	6/12/2010	6/12/2010	-440,000.00
8	RTEYFG	SMPUB915	SMP UBS 092015 CC	152649	DS-PUR	UBSAUD	8/12/2010	8/12/2010	-520,000.00
9
10	RTFDBG	SMPUB915	SMP UBS 092015 CC	154816	DS-PUR	UBSAUD	14/12/2010	14/12/2010	-260,000.00
11	RTFDBG	SMPUB915	SMP UBS 092015 CC	155882	DS-PUR	UBSAUD	16/12/2010	16/12/2010	-570,000.00
12	RTFDBG	SMPUB915	SMP UBS 092015 CC	158291	DS-PUR	UBSAUD	23/12/2010	23/12/2010	-490,000.00
13	RTFDBG	SMPUB915	SMP UBS 092015 CC	159263	DS-PUR	UBSAUD	29/12/2010	29/12/2010	-520,000.00
14
15	RSTBFA	SMPUB915	SMP UBS 092015 CC	160460	INT	INT	31/12/2010	31/12/2010	0.00
16	RSTBFA	SMPUB915	SMP UBS 092015 CC	160656	DS-PUR	UBSAUD	31/12/2010	31/12/2010	-320,000.00
17
18
19
SMPUB915

 

[hiker95]

Isabella,

I can not see the .Borders.Weight = xlHairline formatting in your last post.

And, it looks like you have changed where the formulae go.


Please attach a workbook with the three worksheets, RawData, SMPJPD15, and SMPUB915, completed the way you want to see it.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[Isabella]

Hiker i worked it out myself on what i need to do, i add the below to the code and it works fine.

Thanks for your help on this.

With Worksheets(WSary(a)).Range("A6:M" & NR & ",Q6:R" & NR)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 0
        .Borders.Weight = xlHairline
       End With
        With Worksheets(WSary(a)).Range("N6:P" & NR & ",S6:S" & NR)
            .Borders.LineStyle = xlContinuous
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 24
            .Borders.ColorIndex = 2
        End With

Isabella,

I can not see the .Borders.Weight = xlHairline formatting in your last post.

And, it looks like you have changed where the formulae go.


Please attach a workbook with the three worksheets, RawData, SMPJPD15, and SMPUB915, completed the way you want to see it.

You can upload your workbook to www.box.net and provide us with a link to your workbook.