Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste into Empty Row (Excel 2003)

    I've just found this in the Archives, to copy data into the first empty row.

    Sub CopySomething()
    Worksheets("Source").Range("A1:F1").Copy _
    Destination:=Worksheets("Target").Range("A65536"). End(xlUp).Offset(1, 0)
    End Sub


    I'd like to use something similar, but use PasteValues instead of Copy

    I've tried playing with the syntax, but can't make it work.

    Can someone help please.

    Thanks

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

    Re: Paste into Empty Row (Excel 2003)

    Is this what you need:

    <pre>Sub CopySomething()
    Worksheets("Source").Range("A1:F1").Copy
    Worksheets("Target").Range("A65536").End(xlUp).Off set(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste into Empty Row (Excel 2003)

    Yep that's perfect. Thanks again

    Colin

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste into Empty Row (Excel 2003)

    Another stupid one, as I can't find any help in the Help files for this syntax.

    Same issues, but now instead of pasting values from another field, I want to add for example the date, some fixed text

    The date I want to add is part of a text phrase as below

    "=""Prize "" & TEXT(TODAY(),""Mmm"") & "" "" & TEXT(TODAY(),""YY"") & "" """

    Apologies again

    Colin

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

    Re: Paste into Empty Row (Excel 2003)

    Where do you want to put that, and do you want to put it in a cell as a formula (where the date data will update whenever the worksheet recalculates), or as a text string?
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste into Empty Row (Excel 2003)

    I've got several places to place textin two different tables. The 1st One I can use paste values for all entries.

    The 2nd one though, I'm filling in entries on a balance sheet for balancing later with a bank statement.

    The first entry is just todays date in column 1, This will need to be a date, but not for updating.

    Next the description above "=""Prize "" & TEXT(TODAY(),""Mmm"") & "" "" & TEXT(TODAY(),""YY"") & "" """ to be stored in column 2. this to be stored as TEXT.

    Next is the Payee in column 3 , done as a paste value which I worked out from the last answer

    And so on

    Hope this makes sense


    Colin

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

    Re: Paste into Empty Row (Excel 2003)

    Does this get you started:

    <pre>Dim oCell As Range
    Set oCell = Range("A65536").End(xlUp).Offset(1, 0)
    oCell.Value = Date
    oCell.Offset(0, 1).Value = "Prize " & Format(Date, "mmm yy") & " "
    </pre>

    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste into Empty Row (Excel 2003)

    Sure does. It works perfectly


    Many many thanks

    PS you've no idea how gratifying it is to not only get a detailed answer, but get it quickly, and it works. I love teaching myself this subject, but there are times when I need pointed in the right direction, then I can usually take it from there.

    Thanks again guys

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

    Re: Paste into Empty Row (Excel 2003)

    We enjoy helping everyone, but we really enjoy helping those that want to learn.
    Legare Coleman

Posting Permissions

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