Results 1 to 8 of 8

Thread: VBA - copy and past to next blank cell using another column as my anchor
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - copy and past to next blank cell using another column as my anchor

    Hi, I'm trying to figure out a way to post a date of 05-15-2019 in column AJ and fill down to the last blank cell using column G as my anchor. When I use the code below, it not only puts 05-15-2019 in column AJ but all columns and this date changes each month, this month is 05-15-2019, but next month it's 06-15-2019 and so on. Is there a way to do that, currently I'm putting =NOW() in cell AJ1 and trying to copy that to the first blank cell in AJ and filling down to the last blank cell using G as my anchor, but this code is not working.

    Any suggestions,


    Code:
    Windows("Rental Rec.xlsm").Activate    Sheets("Owned").Select
        lastrow = Cells(Rows.Count, "G").End(xlUp).Row
        Range("AJ1").Select
        Selection.Copy
        lrownew = Cells(Rows.Count, "AJ").End(xlUp).Row + 1
        Range("AJ" & Rows.Count).End(xlUp).Offset(1).Value = "05/15/2019"
        Range(Range("AJ" & Rows.Count).End(xlUp).Offset(1), Range("G" & Rows.Count).End(xlUp).Offset(, 2)).Value = "05/15/2019"

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    You need to change the offset from column G to 29
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    Do you mean like this:

    Code:
    Windows("Rental Rec.xlsm").Activate    Sheets("Owned").Select    lastrow = Cells(Rows.Count, "G").End(xlUp).Row
        Range("AJ1").Select
        Selection.Copy
        lrownew = Cells(Rows.Count, "AJ").End(xlUp).Row + 1
        Range("AJ" & Rows.Count).End(xlUp).Offset(29).Value = "05/15/2019"     Range(Range("AJ" & Rows.Count).End(xlUp).Offset(29), Range("G" & Rows.Count).End(xlUp).Offset(, 29)).Value = "05/15/2019"

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    Nope, just for column G
    Code:
     Range(Range("AJ" & Rows.Count).End(xlUp).Offset(1), Range("G" & Rows.Count).End(xlUp).Offset(, 29)).Value = "05/15/2019"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    It's still not working, its still pasting the date in all columns not just AJ

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    Please define "All columns"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    All columns from G to AJ have the date in each cell down to the last cell using column G as my anchor.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA - copy and past to next blank cell using another column as my anchor

    The code in post#4 does not do that for me. Do you have any merged cells on the sheet?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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