Paul,
You may have seen my response to David already. Thanks again for the information. The "match" method worked really well.
e.g.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Descr = WorksheetFunction.Match("Description", Rows("1:1"), 0)
PN = WorksheetFunction.Match("Part Number", Rows("1:1"), 0)
Sheets("Sheet1").Columns(Descr).Copy Destination:=Sheets("Sheet2").Range("A1")
Sheets("Sheet1").Columns(PN).Copy Destination:=Sheets("Sheet2").Range("B1")
This way makes it so much easier for me to keep track of what I'm bringing into my Results workbook, regardless of its location on the Source workbook.
Regards,
Steve
--- In ExcelVBA@yahoogroups.com, "Paul Vermeulen" <paul.vermeulen@...> wrote:
>
> 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@...
> 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