Dear Patrick
Thank you for your reply. The example I'd given was a really simplified
version of the actual data and I knew it probably wouldn't lend itself
to a written explanation. Sorry if it didn't make sense.
Am I able to send you a file with a small XL example if not then I've
attempted to explain again. Pls note I do not wish to use filters or
pivot tables etc
Worksheet 1 contains data organised across columns rather like fields in
a database. The captions/headers of this row are
A B C D E
F G H
GWP UPR Earned RI CY RI PY Claims CY
Claims PY Expenses
and so on across 95 columns.
Now what I'd like to do is reorganise those columns in a different order
according to the report type, ie whether it's a UKGAAP report , a FR
GAAP report but give the user control via a front-end table rather than
hard-coding anything. Hence on worksheet 2 (called table) I've created
the following
Column A Column B Column C
Desc Report1 Report2
GWP 1
UPR
Earned 3 1
RI CY 6
RI PY 7
Claims CY 4 2
Claims PY 5 3
and so on. The idea is that the number denotes the field reference
number. In actual fact in Columns C, D there will be other definitions
to create a number of reports from the same extract file.
What I'd like the VBA to do is step through column A of the table for
each item with an entry in column B (don't worry about column C 'cause I
know how to refer to that). VBA then needs to remember the description
and related number so that where a match is found against the original
data (on worksheet 1), the detail is transferred so that
A B C D E
F G H
GWP UPR Earned RI CY RI PY Claims CY
Claims PY Expenses
1 3 6 7
4 5
I can then use this row as a basis for resorting my columns, using VBA.
I'm ok with the code to hide/delete unwanted columns but thank you for
your example. Please note I cannot organise my source data in the
correct order to just run the routine by hiding/deleting columns (that
was my original plan) it's a no go because of the differing report
layouts we use. Just trust me on that one.
I hope this makes a little more sense. Any more help would be greatly
appreciated.
Kind regards
Jacqui
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!