• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Add new row to end of table, with yesterday's date

alanj

New Member
Hello,


I'm a VBA newb, and I'd like to create a Macro to add a new row at the end of several tables, while entering the date into the first column of the new row.


Any help?


Best,


Alan
 

Luke M

Excel Ninja
How will the macro know where the tables are? Example ideas:

Table starts in A2 on each worksheet

Tables are named ranges that follow a pattern in naming

Tables all start with the word "FindMe"

etc.
 

Luke M

Excel Ninja
Hmm. Okay, different tactic. Record macro of the following actions:

Select a table, top left.

Hit Ctrl+down arrow to go to end. Insert a new row (copied from above, or are you truly inserting?)

Insert today's date into correct cell.

Repeat for other tables.


This will give the basic outline of the macro. Post that here. Then, we can just go back through, simplifying and adjusting code where needed so as to always do today's date, select next row, etc.
 

alanj

New Member
I did the ctrl+down arrow and then inserted a row, then I grabbed the date from the column before and dragged down the cell to get the new date.


I got the run time error '1004'.


Sub Macro3()

'

' Macro3 Macro

'


'

Selection.End(xlDown).Select

ActiveWindow.SmallScroll Down:=9

ActiveCell.Offset(0, -1).Range("A1:W1").Select

Selection.ListObject.ListRows.Add AlwaysInsert:=True

ActiveCell.Range("A1:B1").Select

Selection.AutoFill Destination:=ActiveCell.Range("A1:B2"), Type:= _

xlFillDefault

ActiveCell.Range("A1:B2").Select

End Sub
 

Luke M

Excel Ninja
Hmm. It appears that you're actually adding rows to lists? In which case, maybe this generic macro will help you?

Code:
Sub AddRows()

Dim wrksht As Worksheet

Dim oListCol As ListRow

Dim x As Integer, i As Integer


'Cycle through each worksheet

For Each wrksht In ThisWorkbook.Worksheets

'check if any lists on this sheet

x = wrksht.ListObjects.Count

If x > 0 Then

For i = 1 To x

'Add new row to list, place today's date in 1st column

Set oListCol = wrksht.ListObjects(i).ListRows.Add

oListCol.Range(1, 1) = Date

Next i

End If

Next wrksht

End Sub
 

alanj

New Member
Very nicely done. I appreciate your time.


Do you know how to make exceptions? There are some tables where adding the date isn't appropriate.


Kind Regards
 

Hui

Excel Ninja
Staff member
Alanj


Change the middle bit

[pre]
Code:
For i = 1 To x
'Add new row to list, place today's date in 1st column
Set oListCol = wrksht.ListObjects(i).ListRows.Add
oListCol.Range(1, 1) = Date
Next i

to:


If Condition Then
For i = 1 To x
'Add new row to list, place today's date in 1st column
Set oListCol = wrksht.ListObjects(i).ListRows.Add
oListCol.Range(1, 1) = Date
Next i
End If
[/pre]
where the Condition is what you need to happen for the row to be added
 
Top