when last day in month and time is 23:55 then copy cells???

[PaSha]

hy guys...

me again... hope i'm not anoying...

yesterday i was traying to copy same range of cells when a day was for example 30... and i managed that..

but know i was traying to complicate this hehe and say... that perform may copy code when the day in month is the last day and the clock is 23:55 ... is that posible ... i can get the code for the day but its stupid i needed to write for every month then this ... but wats with the time??

i have so:If day(Range("j2")) = 30 And Month(Range("j2") 11 Then ... bla bla copy

but not every month has 30 days, and what0s with the time???

becouse i wanna mace it so that when the month is over the data should copy in a special sheet where the months are listed...

so if someone could help me to solve this please...
thanks alreday

[Bob Phillips]

The last day is easy



If Month(Range("J2").Value <> Month(Range("J2").Value + 1) Then

[mdmackillop]

You'll need to fix this for December.

Sub DoCopy()
Dim EndTime, StartTime
EndTime = CDate("1/" & Month(Range("j2")) + 1 & "/" & Year(Range("J2")))
StartTime = EndTime - (1 / 24 / 12)
If CopyTime > StartTime And CopyTime < EndTime Then
'DoStuff
End If
End Sub

[PaSha]

hey guys... thanks for the fast replay...

befor you wrote i was traying to do something what was my plan that this code should do... but now i think i have complicated my live and also i don't understand now, how to integrate your code into myn...

so the problem is this...

i made a code which is not so perfect but it was just begining... and i managed to make it without any mistakes, errors, but when i tray it.. (to change the dates) the code does NOTHING... i don't know why...

and now when you wrote me a replay a don't know how to make this together...

so if someone could please look my mess here and say what is wrong and what to do now???

thanks again





Private sub worksheet_selectionchange(byval target as range)

Dim a as long, b as long, c as long, cSheet as Worksheet, pSheet As Worksheet

'this is the range which should be copied
cPath = Sheets("Woche 1").Range("g20:v20").Value

a = day(Range("j2"))
b = month(Range("j2"))
c = year(Range("j2"))

With Sheets("Woche 1").Select

Select case b

Case 1
Ppath = Sheets("Jahr").Range("c12:r12").Value

Case 2
Ppath = Sheets("Jahr").Range("c17:r17").Value

Case 3
Ppath = Sheets("Jahr").Range("c22:r22").Value

Case 4
Ppath = Sheets("Jahr").Range("c27:r27").Value

Case 5
Ppath = Sheets("Jahr").Range("c32:r32").Value

Case 6
Ppath = Sheets("Jahr").Range("c37:r37").Value

Case 7
Ppath = Sheets("Jahr").Range("c42:r42").Value

Case 8
Ppath = Sheets("Jahr").Range("c47:r47").Value

Case 9
Ppath = Sheets("Jahr").Range("c52:r52").Value

Case 10
Ppath = Sheets("Jahr").Range("c57:r57").Value

Case 11
Ppath = Sheets("Jahr").Range("c62:r62").Value

Case 12
Ppath = Sheets("Jahr").Range("c67:r67").Value


End Select

If c = 2007 And b And a = 30 Then

Ppath = Cpath

End If

End With

End Sub

[PaSha]

maybe i didn't say exact what i want to do...

so you see i comented that range which i want to copy ... and i want that range to be copied when a month is over... becouse in that range there are some value which represent the finished products.... so i would like that this code knows when a month is over and for security reasons copy me this range to another sheet, becouse next working day, there are gonna be new values in that range... so that's why i needed the code for the last day in month and time 23:55 ... becouse then i think it would be the best to copy my range... becouse the values are putted in the hole day and till 00:00.

so hope this is doable what i was imagining... hope you can help me and sorry if i'm anoying

[PaSha]

ahm, ok i managed this code to work, i didn't write the code in the right order... now it works ...



Private Sub worksheet_selectionchange(ByVal target As range)

Dim a As Long, b As Long, c As Long, cSheet As Worksheet, pSheet As Worksheet

'this is the range which should be copied
cPath = Sheets("Woche 1").Range("g20:v20").Value

a = day(Range("j2"))
b = month(Range("j2"))
c = year(Range("j2"))

If c = 2007 And b And a = 30 Then

With Sheets("Woche 1")

Select Case b

Case 1
Sheets("Jahr").Range("c12:r12").Value = cPath

Case 2
Sheets("Jahr").Range("c17:r17").Value = cPath

Case 3
Sheets("Jahr").Range("c22:r22").Value = cPath


Case 4
Sheets("Jahr").Range("c27:r27").Value = cPath


Case 5
Sheets("Jahr").Range("c32:r32").Value = cPath


Case 6
Sheets("Jahr").Range("c37:r37").Value = cPath


Case 7
Sheets("Jahr").Range("c42:r42").Value = cPath


Case 8
Sheets("Jahr").Range("c47:r47").Value = cPath


Case 9
Sheets("Jahr").Range("c52:r52").Value = cPath


Case 10
Sheets("Jahr").Range("c57:r57").Value = cPath


Case 11
Sheets("Jahr").Range("c62:r62").Value = cPath


Case 12
Sheets("Jahr").Range("c67:r67").Value = cPath


End Select

End With

End If

End Sub




ok this kind of code works... but there is problem, i should make more if statements becouse not every month ends on the 30th... some 31,29...

but can i use your code... and then say when the last day of the month is today then perform an action, but equal on which month it is...

???

[Bob Phillips]

Not tested



Private Sub worksheet_SelectionChange(ByVal target As Range)
Dim cPath

'this is the range which should be copied
cPath = Sheets("Woche 1").Range("g20:v20").Value

If Year(Range("J2").Value) = 2007 And _
Month(Range("J2").Value) <> Month((Range("J2").Value + 1)) Then

Sheets("Jahr").Range("C" & 12 + (Month(Range("J2").Value) - 1) * 5).Resize(, 16).Value = cPath
End If

End Sub

[PaSha]

hej guys... me again...

i was traying to solve that problem on a much easier way, becouse that what i began with last month and 23:55 'clock was to complicated...

so i thought why not to make the code from first day in the month...there are no specials threaths becouse every month has the 1st day, so there would be no problems... and i thought why do i not make that in the same wroksheet were this all begins... the start button... when the user clicks on the start button and when the day is the 1st in month then copy the values till this date in previous month..and it works great... and i think it easier...

here is the code (look the second half of the code to see the new code..the first part is the button code... )


Private Sub CommandButton1_Click()


Dim a As Long, b As Long, wb As Workbook, wb2 As Workbook, wbRuckstand1 As Workbook, ws As Worksheet, strPath As String, strSheetName As String
Dim dan As Long, mesec As Long, leto As Long

dan = Range("e11").Value
mesec = Range("e12").Value
leto = Range("e13").Value

'Set Cpath = Workbooks("Ruckstand1.xslx").Sheets("Woche 1").Range("g20:v20").Value

Set wb2 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\ProduktNumern.xlsx")


a = Range("i12").Value
b = Range("i16").Value


strPath = "C:\Users\user\Documents\company\Ruckstand\RuckstandEintrag" & a & ".xlsm"

Set wb = Workbooks.Open(strPath)

With wb


Select Case b

Case 1
strSheetName = "PONEDELJEK-Montag"
Case 2
strSheetName = "TOREK-Dienstag"
Case 3
strSheetName = "SREDA-Mitwoch"
Case 4
strSheetName = "ČETRTEK-Donnerstag"
Case 5
strSheetName = "PETEK-Freitag"
Case 6
strSheetName = "SAMSTAG-Sobota"

End Select

Sheets(strSheetName).Select


End With


If leto = 2007 And dan = 1 And mesec = 12 Then

Set wbRuckstand1 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\Ruckstand1.xlsx")

Workbooks("Ruckstand1.xslx").Sheets("Jahr").Range("c62:r62").Value = Workbooks("Ruckstand1.xslx").Sheets("Woche 1").Range("g20:v20").Value

End If

If leto = 2008 And dan = 1 And mesec Then

Set wbRuckstand1 = Workbooks.Open("C:\Users\user\Documents\company\Ruckstand\Ruckstand1.xlsx")

With Sheets("Woche 1")

Select Case mesec

Case 1
Sheets("Jahr").Range("c67:r67").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 2
Sheets("Jahr").Range("c12:r12").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 3
Sheets("Jahr").Range("c17:r17").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 4
Sheets("Jahr").Range("c22:r22").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 5
Sheets("Jahr").Range("c27:r27").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 6
Sheets("Jahr").Range("c32:r32").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 7
Sheets("Jahr").Range("c37:r37").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 8
Sheets("Jahr").Range("c42:r42").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 9
Sheets("Jahr").Range("c47:r47").Value = Sheets("Woche 1").Range("g20:v20").Value

Case 10
Sheets("Jahr").Range("c52:r52").Value = Sheets("Woche 1").Range("g20:v20").Value


End Select

End With

End If


End Sub


but i was just thinking if someone knows how..now i made this code like this: the workbook opens were the months are stored and copy code is performed... but is there any posibility to make this code and copy stuff perform, without opening that workbook, i trayed that but it brings me some errors... maybe some of you know how to make this ..?? if not, no problem... its also good like this ...


so thanks again all of you...

[PaSha]

hey guys...

i wrote this thread asking you if some action can perform when last day in month is and the hour is 23:55.... then i didn't know how i could do this... now i realised with the hours it is simple...

you just have to make some extra cells and use formulas: HOUR() and MINUTE() which brings you the numerical value of hour or minute...
so if the clock now is 17:34 it would stand: hour: 17 and minute: 34 ... and so on...

and then you can just say in backround code...


If Sheets("Zeit").Range("i16").Value = 23 And Sheets("Zeit").Range("i17").Value = 55 Then
' perform action
End if

where on I16 would stand the value of hour and I17 the value od minute, with those two excel default formulas..

so this is just the part of the time, you should also then calculate last day in month and put it together with that...

so hope this helps someone in future...

greathings and thanks to all who replayed ...