Auto increment Excel rows 1

[CrashBeta]

lets say i wanted to autoincrement the data in excel rows by the day of the month every night at 11:50am how Could i do this ???

 

[byundt]

CrashBeta,
To increment your data on a fixed schedule, you would want to review the discussion and sample code offered by Chip Pearson at

http://www.cpearson.com/excel/ontime.htm

The specific macro that you would need might look like:

'Code goes in regular module sheet, with Public declarations before any subs or functions
Public RunWhen As Double
Public Const cRunWhat = "UpdateDay"

Sub StartTimer()
'You need to pick the next time to run the UpdateDay sub. Use one of the following statements customized to your situation

RunWhen = IIf(Hour(Now) =23, 1, 0) + Int(Now) + TimeSerial(23, 50, 0)     'Run every night at 11:50 PM
'RunWhen = Int(Now) + TimeSerial(Hour(Now) + 1, 0, 0)                'Run every hour, on the hour
'RunWhen = Int(Now) + TimeSerial(Hour(Now), Minute(Now) + 1, 0)      'Run every minute, on the minute
'RunWhen = Int(Now) + Int((Now - Int(Now)) * 1440 / 10 + 1) / (1440 / 10)    'Run every 10 minutes, on the dot
'RunWhen = Now + 5 / 86400      'Run every 5 seconds. With intervals this short, use a Boolean switch to stop the timer

'The next five statements are optional. They create a Boolean switch to control whether the timer runs.
Dim rg As Range
On Error Resume Next
Set rg = Range("StopTimer")
If rg Is Nothing Then ActiveWorkbook.Names.Add "StopTimer", "=TRUE"
On Error GoTo 0

Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True

End Sub

Sub UpdateDay()
Dim i As Long
With Worksheets("Sheet1")      'Change to worksheet name you want to update
    .Cells(65536, 1) = Day(Date)
    .Cells(65536, 1).Copy
    .[A1:B50].PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd      'Change to cell address you want to update
    .Cells(65536, 1).Clear
    i = .UsedRange.Rows.Count
End With
StartTimer

End Sub

Sub StopTimer()
On Error Resume Next
'Range("StopTimer")=False   'Use this statement if time interval between calls is short
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub


'Code goes in ThisWorkbook code pane. It won't work anywhere else!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub

Private Sub Workbook_Open()
RunWhen = IIf(Hour(Now) =23, 1, 0) + Int(Now) + TimeSerial(23, 50, 0)     'Run every night at 11:50 PM
Application.OnTime RunWhen, "UpdateDay"
End Sub

Brad

 

[CrashBeta]

thats awsome but it's putting numbers in two colums not in the row number by the day of the month so if the date is Feb4th it goes on the 4th row across

 

[CrashBeta]

so the layout of the sheet Would be for my data i need to run like this

A16}DAY[B16]DATE[C16]Val1[D16]val2[E16]val3.. etc

4 1/4/2006 35 37 123
5 1/5/2006 75 87 234
6 1/6/2006 234 123 12

my data get pulled in thru DDE Connection inwhich i have already

last day of Month at 11:50 run my macro's i have
Inwhich it saves the sheet by sheet name etc etc
i can share all my 200 VBA lines i have if i can get this part working Correctly,, thanks

 

[byundt]

I'm not following what you expect to happen at 11:50 PM. The code is trying to add the day number to a bunch of cells. It probably sounds daft, but that's what I thought you were requesting in the original question.

Now, it looks like you want to pull in another line of data using your DDE connection. If that's the case, then you just need to change the UpdateDay sub to do the pulling and place the data where you want it.

Brad

 

[SkipVought]

Hi,

You may indeed have a good reason for saving each month's data in a separate workbook, but in doing so, you render the information about as useful as if you were to PRINT and FILE it in a file cabinet.

How can you generate daily, weekly, monthly, quarterly, annual statistics?

How can you compare last year's to this year's statistics?

How can you project forecasts?

How can you analyze trends?

Maybe none of these question will ever be asked, so why even gather the data to begin with???

Data worth gathering, is a corporate asset that is worth storing in a way that can be utilized and leveraged.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[KenWright]

Amen to that

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

[tt]

D
e
s
c
e
n
ding Ahhhhhhh. I feel better now.[/tt]

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[CrashBeta]

Im basicly Pulling in a line of data thru DDE by the day of the month i just need to Put the Line of data in a row by the Day,
DAY DATE DATA
12 1/12/2006 123 123 234 456 123 234 123
13 1/13/2006 123 123 234 456 123 234 123
14 1/14/2006 123 123 234 456 123 234 123
15 1/15/2006 123 123 234 456 123 234 123

at the End of the month at 11:50 < run another Sub

 

[SkipVought]

With Cells(Day(YourDate), "A")
  .Offset(0,0).value = .Row
  .Offset(0,1).value = YourDate
  for i = 0 to 6
    .Offset(0,2+i).value = YourDDE(i)
  next
End With

But, can you answer the above questions I posed?


Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[CrashBeta]

Everything Copied from a daily is transfered over to a yearly sheet then the daily is saved per month And totals
are Copied to a yearly by month e everything is saved
by sheet baises

 

[SkipVought]

So you're doubling the required storage.

And you're isolaing each year's data from every other year's data.

I'd import the DDE data right into a single table containing MULTI YEARS of data...

lNextRow = [A1].CurrentRegion.Rows.Count + 1
With Cells(lNextRow, "A")
  .Offset(0,0).value = Day(YourDate)
  .Offset(0,1).value = YourDate
  for i = 0 to 6
    .Offset(0,2+i).value = YourDDE(i)
  next
With

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}