Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Column Copy/Paste (XP; 03)

    I have a snippet of code that copies three columns from the current workbook and then is suppose to paste them into a new workbook. I can not determine why it is not functioning properly. The two lines of code are:

    oCurWB.Sheets("MySheet").Columns("A:C").Copy
    oNewWB.Sheets("MySheet").Columns("A:A").Paste 'Line challenged here. It has to do something with ".Paste"

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Copy/Paste (XP; 03)

    Try:

    <code>
    oCurWB.Sheets("MySheet").Range("A:C").Copy
    oNewWB.Sheets("MySheet").Paste Destination:=oNewWB.Sheets("MySheet").Range("A1")
    </code>
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Copy/Paste (XP; 03)

    Legare,

    You suggestion worked. One thing though, the formulas that were copied/pasted from the oCurWB to oNewWB are being referenced or linked back to the oCurWB.

    As an example formula in oCurWB:
    A1 = +MySheet2!D40

    oNewWB also has an identical sheet named "MySheet2". When the columns are copied/pasted over, the formula links back to oCurWB which is not what I'm after
    A1=+'[oCurWB]MySheet2'!D40

    The formula in oNewWB should be:
    A1 = +MySheet2!D40


    Regards,
    John

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Copy/Paste (XP; 03)

    That is the way Excel works, it keeps the formula pointing at the same cells. The only way I can think of to get around this problem would be to not use copy and paste, but to loop through the area and test each cell for having a formula. If it contains a formula, then move the formula to a string variable and then store the string variable into the formula property of the destination, and if it does not contain a formula you would move the value property. You would also have to copy and paste the formatting to get that to move to the new WB.

    Maybe someone else has a better solution.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Column Copy/Paste (XP; 03)

    You probably could do it without testing for a formula. You should be able to loop thru the collection and place the formula property of the source into the formula of the target. If it does not have a formula the formula property is the same thing as the value.

    Steve

Posting Permissions

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