Steve, the VBA code to do that would be to search the top row for the
correct text, and return the column for when it has been found.
E.g.
Dim i as Variant
i = find("Text","A1:ZZ1").column
Then use i in you copy command as the column reference.
You may also prefer "match" rather than "find".
Regards
Paul
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of david.smart@ubs.com
Sent: Tuesday, 21 February 2012 6:59 AM
To: ExcelVBA@yahoogroups.com
Subject: RE: [ExcelVBA] Copy and Paste Columns from one Workbook to
Another, based on Column Heading
I do this a lot when I'm reading workbooks into Java, but the techniques
would work OK for VBA as well. I've used a couple of methods, and don't
really have a "favourite".
The first method is to put range names on the header row, so that each
cell in that row has a range name that is as similar to the text in the
cell as possible. (I actually wrote a small subroutine to generate the
range name, based on the content.) From then on, I use the range name
to determine the column and the row number to determine the row.
Putting these into a Cells() call will get me to the cell with minimum
fuss.
The second method is to use a small function that looks up the column
number, given the text that is in the header row. It knows where the
header row is, and simply scans across it until it finds the required
heading. Then it returns the column number for use in the Cells() call.
In Java, I certainly find it beneficial for both methods to keep a list
of columns that have already been found, and return the column number
from there, rather than doing the lookup again. (Java helps this by
providing structures that index quickly by a text field. Also, it
avoids going back to the Excel file structure multiple times, which is
relatively slow.) There could well be gains to be made by doing this in
VBA too, especially if the number of columns used is smaller than the
number of columns in the row.
The clear limitation of the first method is that it assumes that people
rearranging the columns will do so "properly" - i.e. by using cut and
paste, rather than copying or overtyping. This might well be out of
your control, and can give some "interesting" results.
The second method is vulnerable too, in that column headings might
undergo subtle changes that stop the columns being found. However, at
least your lookup code can provide error reports for that.
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ]
On
Behalf Of sspatriots
Sent: Tuesday, 21 February 2012 05:39
To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
Subject: [ExcelVBA] Copy and Paste Columns from one Workbook to Another,
based on Column Heading
Hi,
I currently have a macro that will open up a source workbook and copy
several columns of data and then go back and paste those columns into my
results workbook. It will then open a second source workbook and copy
the same columns again and go back and paste those columns into my
results workbook beneath the previous data that was imported. Then this
is repeated once more. So there are a total of 3 different workbooks
where I copy a set group of columns and bring them back to my results
workbook with the data of each appended beneath the previous all on one
worksheet.
The problem I'm faced with now is that because my macro copies defined
ranges, some of the columns in the source workbooks get moved around
sometimes when new ERP changes are pushed out that I don't know about,
hence all of my data coming in is no longer what it was because things
get shifted.
I need to be able to copy in columns of data from the source workbook
based on the column headers. That way, no matter where a column of data
may get moved to in the source workbooks when an ERP update is pushed
out, I still get the same data.
Any ideas on how to get there?
Thanks,
Steve
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar