David,
Thanks for the information. I started down the path of the second method you mentioned last night and it worked out pretty good. I ended up temporarily copying the columns I wanted to a newly created Sheet2 using the "match" option that Paul Vermeulen put in his response. Then when Sheet2 looked like what I wanted, my macro copied that over to my Results workbook.
As always thanks so much for you help, much appreciated.
Regards,
Steve
--- In ExcelVBA@yahoogroups.com, <david.smart@...> wrote:
>
> 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@yahoogroups.com] On
> Behalf Of sspatriots
> Sent: Tuesday, 21 February 2012 05:39
> To: ExcelVBA@yahoogroups.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]
>
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