Insert/Add Current Date in Range

I have a worksheet where I load data into it everyday using code. Column A cannot have any blank cells. For this reason, depending on the amount of rows the worksheet loads on that day, I want to insert the current date for that range based on column A (primary columnn) using a Macro. The loaded data is appended each day. For this reason I do not know how to go abouts coding in new date as data is loaded on a new day.

Thank You,

Ravi Matharoo

Re: Insert/Add Current Date in Range

more of what I need is date in the M column, I already have the date format that will be inserted, but its a matter of finding the days range that will be inserted. According to the amont of rows in the range, I want to insert my date.

for example, if In day 1 there are X rows inserted into my worksheet, I need to find out how i would go abouts counting those amount of rows, and based on that, have the date insert into column M using a macro, based on the amount of X rows. So if there 40 rows inserted, I want to count the rows, and insert date for the 40 rows in column M

In day 2 to lets say i have X rows append to my worksheet, I need to count how many rows were inserted, and based on that, insert the date based on those X rows.

The rows are more or less inserted as a group using a macro. Gor each click I will get X amount of rows inserted in my sheet. What I need is, based on the click of a new macro, a way to find out how many rows are inserted for that group, thats why I mentioned that Column A willl always have data as it is my data lookup function. Maybe that can come in use in determining the row count, and from there insert the new date for the X amount of rows

Thank You,

Raci Matharoo

Re: Insert/Add Current Date in Range

I will try to provide as much detail I can, so here goes.

1) I have a workbook that contains 5 worksheets.
- I use internet resources to load up the worksheets each day, all in table format.

- I have a master sheet "Merged Data" where I am transferring all my data into each day using Match Lookup. "Symbol" column is the lookup column. All the data that is transferred to merge data sheet is appended.

I clear the 5 worksheets on a daily basis.

Click of a Macro provides the following:
Here is the code I am using to merge the 5 worksheets

Private Sub wkscmd_MergeData_Click()
Application.Calculation = xlCalculationAutomatic
'  Local Variables
   Dim rngData       As Range, cell          As Range
   Dim strSyms       As String, strMsg       As String
   Dim intCount      As Integer
   Dim itm           As Variant
   Dim wsMrgd As Worksheet   'new
   Dim rngTkr As Range  'new
   Dim lstRow As Integer
   Dim k As Integer
   Set wsMrgd = Sheets("Merged Data") 'new
   Set rngTkr = wsMrgd.Range("A10", wsMrgd.Range("A9").End(xlDown))
    'Make a unique list of symbols available in Merged Data (new)
 
   For intCount = 1 To 5
      Select Case intCount
         Case 1: Set rngData = Worksheets("DATA 1").Range(Worksheets("DATA 1").Range("A5"), Worksheets("DATA 1").Range("A5").End(xlDown))


         Case 2: Set rngData = Worksheets("DATA 2").Range(Worksheets("DATA 2").Range("A5"), Worksheets("DATA 2").Range("A5").End(xlDown))


         Case 3: Set rngData = Worksheets("DATA 3").Range(Worksheets("DATA 3").Range("A5"), Worksheets("DATA 3").Range("A5").End(xlDown))


         Case 4: Set rngData = Worksheets("DATA 4").Range(Worksheets("DATA 4").Range("A5"), Worksheets("DATA 4").Range("A5").End(xlDown))


         Case 5: Set rngData = Worksheets("DATA 5").Range(Worksheets("DATA 5").Range("A5"), Worksheets("DATA 5").Range("A5").End(xlDown))
      
      
      End Select
      
      For Each cell In rngData
         If InStr(strSyms, "," & UCase(cell) & ",") = 0 Then
            strSyms = strSyms & "," & UCase(cell) & ","
         End If
      Next cell
   Next intCount
 
'  Transfer data from data sheets
   
   For Each itm In Split(strSyms, ",")
      If itm <> "" Then
           Worksheets("MERGED DATA").Range("merged_DataLookup") = itm
           With Worksheets("MERGED DATA").Range("A65536").End(xlUp).Offset(1, 0)
              .EntireRow = Worksheets("MERGED DATA").Range("merged_DataLookup").EntireRow.Value
         End With
      End If
   Next itm


   Worksheets("MERGED DATA").Range("merged_DataLookup").ClearContents
   
  wkscmd_MergeNRD_Click
  ChekAddition_Deletion
   
End Sub

- Is there a way, when In this code, as data is being transferred from each sheet to merge data sheet on a per row basis that the date is inserted in column "er" (or at the end of the row; either/or) in merged data sheet.

For example, if in day 1 I click merge, X amount of data is inserted as well as the Day 1 date in column er (or end of the row); I want the date to insert based on the amount of "Symbols" I get for that day. In day 2, I get new data and click merge again, Day 2 should have now Day 2 date in column "er" of merge sheet again depending on the amount of symbols.

- Right now I have an insert date function which inserts date into 2 worksheets. But in the merged datasheet, I can only merge using only 1 date column of the 2 datasheets. I can only link a column to another column in the datasheets; I suppose I cant link 2 date columns to 1 date column in the merged sheet

So if I have a symbol aaa in sheet 1 and symbol zzz in sheet 2, depending on the link for the sheet I use to receive date data for merge sheet, it will only choose date from one sheet. When I click merge, one row will show date in er column and the other row wont.

here is the coding that inserts date into each sheet seperately. But like I said, I want it to insert in the merge datasheet to avoid this situation.

Sub Button1_Click()
    strMsg = "Insert todays date?"
    strResp = MsgBox(strMsg, vbYesNo, "Update Date")
    strDate = InputBox("Enter Date", , Date) 'Date
    strDate = Format(strDate, "MMM DD, YYYY")
    If strResp = vbNo Then Exit Sub
    For i = 1 To 2
    lstCell = Cells.Rows.Count
    Select Case i
        Case 1: Sheets("Data 1").Range("m5", _
            Sheets("Data 1").Range("A" & lstCell).End(xlUp).Offset(0, 12)).Value = strDate
        Case 2: Sheets("Data 4").Range("BU5", _
             Sheets("Data 4").Range("A" & lstCell).End(xlUp).Offset(0, 73)).Value = strDate
    End Select
    Next
End Sub

Thank You,

Ravi Matharoo