Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy/Paste column data (Excel 2000)

    I've been playing with this "utility" routine for some time, but I'm not happy with it in terms of the "good coding" practices I've read about in these lounges. I'm trying to do a rewrite and totally avoid using selection and activation/ activate, and I really can't see how it can be done. The situation is as follows:

    I have 2 worksheets - shtSource and shtTarget - in separate workbooks - wbSource and wbTarget. Selected columns are to be copied from source into designated target columns. The column correspondence information is stored in an array so that, for example, tCol(5) = 8 means that column E (=5) of the target sheet gets the data from column H (=8) from the source sheet. Each sheet has its (known) "table header" rows so that copying from the source sheet might involve copying the entire column (maybe blank cells included) but not rows 1 & 2, and pasting (paste values actually) to the target might be to a cell in row 2.

    I have done this in a number of ways, but I'm trying for the "cleanest" code, avoiding the select and activate methods. I'm also stuck on the best way to define the range of an entire column, but excluding say the first two cells. Any ideas appreciated.

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste column data (Excel 2000)

    Is it possible to use named ranges - particularly in the Source Worksheet?
    Gre

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy/Paste column data (Excel 2000)

    Something like this?

    Sub CopyColumns()
    Dim wbSource As Worksheet
    Dim wbTarget As Worksheet
    Dim lngStartRow As Long
    Dim lngNumCols As Long
    Dim tCol() As Long
    Dim i As Long

    On Error GoTo ErrHandler

    Set wbSource = Worksheets("Sheet1")
    Set wbTarget = Worksheets("Sheet2")

    lngStartRow = 3

    lngNumCols = 3
    ReDim tCol(1 To lngNumCols)
    tCol(1) = 3
    tCol(2) = 6
    tCol(3) = 1

    For i = LBound(tCol) To UBound(tCol)
    wbSource.Range(wbSource.Cells(lngStartRow, tCol(i)), wbSource.Cells(65536, tCol(i)).End(xlUp)).Copy
    wbTarget.Cells(lngStartRow, i).PasteSpecial Paste:=xlPasteValues
    Next i

    ExitHandler:
    Application.CutCopyMode = False
    Set wbTarget = Nothing
    Set wbSource = Nothing
    Erase tCol
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste column data (Excel 2000)

    Hans, I think you've hit on it! The part I was struggling with was the method for defining the range to be copied:
    wbSource.Range(wbSource.Cells(lngStartRow, tCol(i)), wbSource.Cells(65536, tCol(i)).End(xlUp)).Copy

    I think I'd used all of these at one time or other, but obviously not in the right combination <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I can't quite fathom the Range parameters, but hopefully the VB Help file will explain. I had also tried to find the last used row and use that as an extent, but had no success with that. I also think I tried to avoid using the 65536 value because I thought it would be slower, just like when I tried it with entire column selections.

    But thanks - I'm itching to try it on the latest batch of "messy" sheets I have to convert.

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste column data (Excel 2000)

    I had toyed with using named ranges, but unfortunately there is no consistency amongst the source sheets. The only thing "correct" about them is that data appears in the appropriate column in accordance with its label (where each column appears on the sheet is anybody's guess). This is why I need my little array, which can be changed quickly "on the fly" to accomodate each "mess" as it comes along. Although no doubt possible, I don't think it would be of any practical advantage using named ranges in this situation.

    cheers

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy/Paste column data (Excel 2000)

    Hi Alan,

    One of the ways to use Range is Range(Cell1, Cell2); this returns the rectangular area with opposite corners Cell1 and Cell2. In the code, Cell1 and Cell2 are in the same column, so the rectangular area is one column thin.

    Cell1 is the cell in the row designated as start row (to avoid the header rows) and in the column indicated by tCol(i).
    Cell2 is the last filled cell in the same column; it is found by starting in the last possible row (65536) and using the End function to move up to the last filled cell; this is equivalent to pressing the End key, then the Up arrow, with the exception that the cell is not selected, only a reference to the cell is returned.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste column data (Excel 2000)

    Ah, now I comprendi where the .End(xlUp) actually belongs. Your explanation of this is far better than in the help file, Hans. I was always averse to using these methods because I'd thought they effectively took the biggest range possible (right down to 65536) and that would of course, slow things down. I can see now that it's a method for actually locating the last filled cell, as you explain. I will now use this method with gay abandon <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Many thanks as always.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •