Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy & Paste Method (Excel 2000)

    Good Morning,

    I hope someone can help me here. I need a method of copying and pasting the LAST ROW of records from COLUMN A to C of worksheet COPD to the LAST ROW of records to worksheet DIABETES.

    I have a command button that pops up a userform where the user can select what worksheet they would like to switch to. At this point I would like for the last row of records of the current worksheet they are in from column A to C to be copied then pasted to the worksheet the user selects to go to.

    Hope that explanation was clear and someone can point me in the right direction.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Copy & Paste Method (Excel 2000)

    Roberta,

    If you really want to copy to the last row in the destination worksheet, i.e. overwrite the last row, you can use this code:

    ' Declare object variables
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range

    ' Normally you would set these from your userform
    Set wshSource = Worksheets("COPD")
    Set wshTarget = Worksheets("DIABETES")

    ' Get last row in source, as determined by column A
    Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 3)

    ' Get last row in target, as determined by column A
    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Resize(1, 3)

    ' Copy and paste
    rngSource.Copy Destination:=rngTarget

    ' Release object memory
    Set rngTarget = Nothing
    Set rngSource = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing

    If you actually intend to paste the data to the first empty row, change the line Set rngTarget = ... to

    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 3)

    The extra .Offset(1,0) moves the target one row down. Note: I have assumed that each record contains data in column A. If there are gaps in column A, the code would have to be expanded a bit.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste Method (Excel 2000)

    Hans,

    As always, works perfectly. Can I ask one more thing, Please. Is it possible to have this PASTESPECIAL? I only want values, some of the worksheets have different fonts, backgrounds, etc and when this code works, whatever worksheet it's coming from, everything comes with it. Therefore, PASTESPECIAL would be best.

    Thanks so much for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Copy & Paste Method (Excel 2000)

    Roberta,

    Replace the line

    rngSource.Copy Destination:=rngTarget

    with these two:

    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues

    (or if you want to transfer number formats too, use xlPasteValuesAndNumberFormats)

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste Method (Excel 2000)

    Hans,

    Thanks so much.....much appreciated!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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