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

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
104
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"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,591
Office Version
365
Platform
Windows
You need to change the offset from column G to 29
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
104
Do you mean like this:

Code:
[COLOR=#333333]Windows("Rental Rec.xlsm").Activate    Sheets("Owned").Select[/COLOR]    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" [COLOR=#333333]    Range(Range("AJ" & Rows.Count).End(xlUp).Offset(29), Range("G" & Rows.Count).End(xlUp).Offset(, 29)).Value = "05/15/2019"[/COLOR]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,591
Office Version
365
Platform
Windows
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"
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
104
It's still not working, its still pasting the date in all columns not just AJ
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,591
Office Version
365
Platform
Windows
Please define "All columns"
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
104
All columns from G to AJ have the date in each cell down to the last cell using column G as my anchor.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,591
Office Version
365
Platform
Windows
The code in post#4 does not do that for me. Do you have any merged cells on the sheet?
 

Forum statistics

Threads
1,085,217
Messages
5,382,402
Members
401,787
Latest member
hDalal

Some videos you may like

This Week's Hot Topics

Top