Populate to Last row - Simplify copy

Ananthak275

New Member
Joined
Aug 22, 2020
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
Hey everyone,
My code goes into Column A, starts at row 2 and populates the values based on the last row in Column L.
1. I'm trying to make so instead of column B, it looks for the last row in the sheet, rather than a specific column.
2. Also is there a way to simplify it?

Populate_Column = "A"
DataStartRow = 2
LastRowColumn = “B"

Range(Populate_Column & DataStartRow).Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "yyyy/mm"
Selection.AutoFill Destination:=Range(Populate_Column & DataStartRow & ":" & Populate_Column & Range(LastRowColumn & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
 

Ananthak275

New Member
Joined
Aug 22, 2020
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
Thanks for the feed back and glade that it worked perfect for you

try this, and
VBA Code:
.NumberFormat = tdate
This is what i have and its giving me an error. it says unable to set the numberformat property of the range class
Public tdate as date

tdate = format(Date,"yy-dd"mm")

With Worksheets("sheet1") ' You can change the sheet name
With .Range("A2:A" & .Range("B" & .Rows.Count).End(xlUp).Row)
.NumberFormat = tdate
'.Value = .Value
End With
End With
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Ananthak275

New Member
Joined
Aug 22, 2020
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
You originally said

But the code from vmjan02 is still using column B to find the last row. Yet you say it works perfectly. :unsure:
You're right. My apologies, I used your code and it worked as well thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Ananthak275

New Member
Joined
Aug 22, 2020
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
Glad we could help & thanks for the feedback.
Can you can solve this problem, im not sure how to fix it? it says unable to set the numberformat property of the range class

Public tdate as date

tdate = format(Date,"yy-dd"mm")

With Worksheets("sheet1") ' You can change the sheet name
With .Range("A2:A" & .Range("B" & .Rows.Count).End(xlUp).Row)
.NumberFormat = tdate
'.Value = .Value
End With
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub Ananthak()
   Dim UsdRws As Long
   Dim tdate As String
   tdate = Format(Date, "yy-dd-mm")
   
   UsdRws = Cells.Find("*", , , , xlByRows, xlPrevious, , False).Row
   Range("A2:A" & UsdRws).Value = tdate
End Sub
 
Solution

Ananthak275

New Member
Joined
Aug 22, 2020
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
How about
VBA Code:
Sub Ananthak()
   Dim UsdRws As Long
   Dim tdate As String
   tdate = Format(Date, "yy-dd-mm")
  
   UsdRws = Cells.Find("*", , , , xlByRows, xlPrevious, , False).Row
   Range("A2:A" & UsdRws).Value = tdate
End Sub
Hi sorry one more question... I have this code that finds where the data should start. The part called RelatedColumn is specifying a specific column to find to see where the data should start (+1 because there is headers). I’m trying to not use a specific column to identify where my data should start.

Is it possible to use the UsdRows instead?
RelatedColumn=“C”
StartRow = Workbooks(homefile).Sheets(homefile_sheet).Range(RelatedColumn & Rows.Count).End(xlUp).Row + 1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you still need help with this?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
So do you want to insert the date from the last used row +1 in col C & copy it down to the last used row in the sheet?
 

Ananthak275

New Member
Joined
Aug 22, 2020
Messages
20
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
the other columns will be updated. so i want the data everytime its run to be updated to the last row.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,471
Messages
5,636,510
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top