[Excel] Archiving a Data Row.
Hello,
I would like to be able to archive data rows containing dates and times from columns "J" to "V" in my table on sheet "A" (composed of columns "A" to "AH").
The trigger condition for archiving is that a time must be entered in column "U".
The archiving should take place on sheet "B", with each archived row being placed one below the other starting from cell "A5".
I am quite familiar with Excel but unfortunately not with its programming system! Could you please help me?
Thank you,
Alain
I would like to be able to archive data rows containing dates and times from columns "J" to "V" in my table on sheet "A" (composed of columns "A" to "AH").
The trigger condition for archiving is that a time must be entered in column "U".
The archiving should take place on sheet "B", with each archived row being placed one below the other starting from cell "A5".
I am quite familiar with Excel but unfortunately not with its programming system! Could you please help me?
Thank you,
Alain
15 answers
- Votes
- Date
- Descending date
-
JvDoHello,
it must be very clear to you, but I'm not sure from your writing if you want to retrieve rows or cells (between J and V), and maybe only those that are dates or times.
As for your column U, which identifies the areas to be archived, what is the time criterion?
Last point, can an archived area be archivable again?
If not, how do you differentiate them?
The simplest thing for everyone is for you to upload a sample of your data on the Cjoint site.
Best regards. -
Anonymous userre :
In any case, it seems imperative to establish an index column
that will always have a distinct value, like a number that auto-
increments.
To simplify the work, for example, I could add a new
column [ a - aj ], and I would write the row number of the archive.
That is to say, when writing the archive onto the archive sheet,
there would also be a backup of the row number from the archive sheet
on the source sheet that would provide the address in case of an "overwrite" update
of the archive.
Does this solution seem acceptable to you?
Lupin -
Anonymous userHello,
Indeed, a lot of information is missing!
Assuming:
that the name of sheet A is [ SheetA ]
that the name of sheet B is [ SheetB ]
that a record consists of columns A to AH
that the data of the record is located between J and V
that the starting cell for archiving is A5
that column [ J ] is never empty
that column [ U ] has a standard time format [ hh:mm:ss ]
that once the data is archived, the complete row of sheet A will be deleted
an example of code to place behind sheet A:
Option Explicit ' Const SheetWork = "SheetA" Const SheetArch = "SheetB" ' Private Sub Worksheet_Change(ByVal Target As Range) Dim Region As Range, RangeString As String, Position As Long, Time As Date ' Do not display processing Application.ScreenUpdating = False ' Find last row Position = Range("J2:J65535").End(xlDown).Row ' Search for valid intersection Set Region = Application.Intersect(Range("U1:U" & Position), Target) If Region Is Nothing Then 'MsgBox "The target is not in the target range." Else ' MsgBox "The target is in the target range." ' If time greater than 8 hours Time = Target.Value If (Time > "08:00:00") Then ' Disable event Application.EnableEvents = False ' Define range to copy RangeString = "J" & Target.Row & ":V" & Target.Row ' Select the range to copy Range(RangeString).Select ' With selection With Selection ' Place in clipboard .Copy ' Call function CopyToArchive End With ' Disable copy/paste Application.CutCopyMode = False ' Select the target cell Target.Select ' Delete the row that has been archived Target.EntireRow.Delete ' Reactivate events Application.EnableEvents = True End If End If ' Update display enabled Application.ScreenUpdating = True End Sub ' Function CopyToArchive() ' Range [ J , V ] to range [ A , AH ] ' Triggered on time condition, column [ U ] ' Starting storage case Sheet B [ A5 ] Dim RangeString As String, Position As Long ' Define the first free row Position = Sheets(SheetArch).Range("A5:A65535").End(xlDown).Row + 1 RangeString = "A" & Position ' Copy data to the line Sheets(SheetArch).Range(RangeString).PasteSpecial RangeString = "A" & (Position + 1) ' Select the next cell Sheets(SheetArch).Select ActiveSheet.Range(RangeString).Select ' Return to SheetA Sheets(SheetWork).Select End Function '
This code works in my environment, be careful!
Lupin-
Alain3 AuthorHello,
Not seeing how to send you a copy of the program, I will try to clarify my intentions.
The true name of sheet "A" is "HKM GMN"
The true name of sheet "B" is "Archivage"
The rows of the table extend entirely between "A" and "AH".
The data to be archived is contained between "J and V".
The starting cell of the "Archivage" sheet is "A5".
Column "J" is never empty.
Column "U," which triggers archiving, is indeed a time in
"hh:mm".
Once archiving is done, the data from the "HKM GMN" sheet must not be deleted but rather copied.
Here is a small example of data between columns "J and V":
44536 10-10 23:52 10-10 23:45 9 8 -00:07 11-10 00:11 11-10 01:39 + 00:28.
Explanations:
44536 = train number.
10-10 = theoretical arrival date
23:52 = theoretical arrival time.
10-10 = actual arrival date.
23:45 = actual arrival time.
9 = delay code.
8 = same.
-00:07 = difference between theoretical and actual arrival; a formula occupies this data and must also be copied.
11-10 = theoretical departure date.
00:11 = theoretical departure time.
11-10 = actual departure date.
01:39 = actual departure time.
+ 00:28 = difference between theoretical and actual departure; a formula occupies this data and must also be copied.
Hoping to have filled your uncertainties, I remain at your disposal for further information.
Best regards,
Alain.
-
-
Anonymous userHello,
There is missing information:
As JvDo mentioned:
... Can an archived area be archivable again?
If not, how do you differentiate them?
I quote:
... Once the archiving is done, the data on the "HKM GMN" sheet should not be deleted but only copied.
Is there a column that indicates whether archiving has occurred?
Lupin-
Alain3 AuthorHello,
And thank you for your patience!
It would indeed be good to be able to re-archive data areas in order to correct some possible errors.
There is no column provided to mark the archiving, but I can easily insert one between columns "V" and "W" on the "HKM GMN" page, thus outside the archiving area. The appearance of the letter "A" in this column to mark the archiving should suffice. Of course, the re-archiving must then "overwrite" the old erroneous line on the "archive" sheet.
The table on the "HKM GMN" page will then extend from column "A" to "AI". The area to be archived remains unchanged.
Talk soon and thanks again.
Alain
-
-
Anonymous userHello,
you are adding another condition that changes everything!
I quote:
Of course, re-archiving must then "overwrite" the old incorrect line on the "archive" sheet.
---------------
Given that the first specification was to archive from
A5, now we need to locate the archive for rewriting.
Therefore, a key field is required, specifying a non-empty column
that is always distinct (different values) which will serve as a
"primary key" for both sheets.
This will make it easier to query to locate the archive, it can be
coded (modified) at the beginning of the program.
Lupin-
Alain3 AuthorHello,
I received your last message, quite technical, and I don't understand all the nuances! The important thing is that you know what it's about. I didn't imagine that my last request would disrupt the entire scheduling. I was just trying to respond to your previous message.
So please do what’s best...
I remain at your disposal for any additional specifications.
Alain3.
-
-
Anonymous userHello,
So here’s what it looks like:
The code to place behind the sheet:
Option Explicit ' Const Cod_Archive = "A" Const FeuilleTrav = "HKM GMN" Const FeuilleArch = "Archivage" ' Private Sub Worksheet_Change(ByVal Target As Range) Dim Region As Range, Position As Long, Temps As Date Dim Adresse As String, Plage As String, NbrArc As Long Dim Valeur As Variant Application.ScreenUpdating = False Position = Range("J2:J65535").End(xlDown).Row Set Region = Application.Intersect(Range("U1:U" & Position), Target) If Region Is Nothing Then 'MsgBox "The target is not within the specified range." Else 'MsgBox "The target is within the specified range." Temps = Target.Value If (Temps > "08:00:00") Then Valeur = Range("Ai" & Target.Row).Value Valeur = Mid(Valeur, (Len(Cod_Archive) + 1)) Valeur = Val(Valeur) If (Valeur > 0) Then Valeur = Range("AJ" & Target.Row).Value Else Valeur = 0 End If Application.EnableEvents = False Plage = "J" & Target.Row & ":V" & Target.Row Range(Plage).Select With Selection .Copy Adresse = CopieVersArchive(Valeur) If (Adresse <> "") Then Range("AJ" & Target.Row).Value = Adresse Adresse = Mid(Range("Ai" & Target.Row).Value, (Len(Cod_Archive) + 1)) If (Adresse <> "") Then Range("Ai" & Target.Row).Value = Cod_Archive & (Adresse + 1) Else Range("Ai" & Target.Row).Value = Cod_Archive & "1" End If Else Range("AJ" & Target.Row).Value = "" End If End With Application.CutCopyMode = False ActiveCell.Offset(1, 0).Select Target.Select Application.EnableEvents = True End If End If Application.ScreenUpdating = True End Sub ' Function CopieVersArchive(ByVal PlgTmp As Variant) As String Dim Plage As String, Position As Long Position = Sheets(FeuilleArch).Range("A4:A65535").End(xlDown).Row + 1 If (PlgTmp = 0) Then Plage = "A" & Position Else Plage = PlgTmp End If Sheets(FeuilleArch).Range(Plage).PasteSpecial Plage = "A" & (Position + 1) Sheets(FeuilleArch).Select ActiveSheet.Range(Plage).Select Sheets(FeuilleTrav).Select If PlgTmp = 0 Then CopieVersArchive = "A" & Position Else CopieVersArchive = PlgTmp End If End Function '
If you try to copy and paste into a cell in column U, the event is triggered and it crashes, it’s normal there’s no event handling which is quite heavy...
If you need to stop the macro, you’ll need events to be disabled, you’ll need this to reactivate:
Sub ActiveEvenement() Application.EnableEvents = True End Sub '
And to disable the triggering of events:
Sub DesactiveEvenement() Application.EnableEvents = False End Sub '
Column [ AI ], the letter A coded in the constant [ Cod_Archive ]
followed by a number indicating the number of times archived.
Column [ AJ ], address (letter number) of the archived row
Have fun :-)
Lupin -
Anonymous userHello,
The code must be placed behind the sheet HKM GMN
a.)
Note: Validate the sheet names at the beginning of the code,
the compiler (interpreter) is case-sensitive.
b.)
When copying and pasting, be careful during line breaks in the forum editor, they should appear in red in the VBA editor.
In the VBA editor, there is a code window to the right
and two windows to the left, the one on top lists the objects
of the project. You can find 4 types of objects there
Sheets
ThisWorkbook
Function (Module)
Class Module
You need to locate the HKM GMN Sheet object, double-click
the editor will then open the corresponding code window for the object on the right.
c.)
The two small routines to activate/deactivate events
must be placed in a module to be accessible from the Excel menu
> Tools/Macro/Macros...
Good luck
Lupin-
Alain3 AuthorHello,
I've placed the program behind the "HKM GMN" sheet.
On the first attempt, nothing happened with a trigger time earlier than 08:00. With a trigger time later than 08:00, the program responds: compilation error, syntax error. The program crashes after selecting the area to copy. I noticed in the programming a conditional time close "if 08:00 then..." What's going on?
On the second attempt, I corrected the casing of the beginning of the program
and FeuilleArch = "Archivage" became FeuilleArch = "ARCHIVE" as in the program. Now, the program doesn't crash anymore, but nothing happens?
Best regards,
Alain3
-
-
Anonymous userre:
Here’s what you need to understand:
Indeed, I forgot to change the name of the archive sheet,
my bad, I apologize!
Once the program crashes, it is almost certain that the
events are disabled due to the line:Application.EnableEvents = False
You need to reactivate the workbook events using the routine:Sub ActiveEvenement() Application.EnableEvents = True End Sub '
This must be pasted into a module to be accessible from the Excel menu.
Excel Menu ->> //Tools/Macro/Macros.../ActiveEvenement
Lupin-
Alain3 AuthorGood evening,
The reactivation macro is working.
After changing "Archive" to "ARCHIVE" in the programming, the debugger stops at: (Sheet ARCHIVE) as shown below and gives the code: "compilation error: variable not defined".
The program stops after selecting the range to copy.
Function CopieVersARCHIVE(ByVal PlgTmp As Variant) As String
Dim Range As String, Position As Long
Position = Sheets(SheetARCHIVE).Range("A4:A65535").End(xlDown).Row + 1.
Best regards,
Alain3.
-
-
Anonymous userre:
So here it is, you must not change the names of the constants:
Function CopyToARCHIVE(ByVal PlgTmp As Variant) As String
Dim Range As String, Position As Long
Position = Sheets(FeuilleARCHIVE).Range("A4:A65535").End(xlDown).Row + 1.
it is and must remain:
Function CopyToArchive(ByVal PlgTmp As Variant) As String
Dim Range As String, Position As Long
Position = Sheets(FeuilleArch).Range("A4:A65535").End(xlDown).Row + 1
it's rather at the definition of the constant that you need to change the name
of the sheet:
at the beginning of the code you have the following expression:
Const SheetWork = "HKM GMN"
Const SheetArch = "Archivage"
'
will become more like:
Const SheetWork = "HKM GMN"
Const SheetArch = "ARCHIVE"
'
Lupin -
Anonymous userre:
I think what will be simpler is to test my example
in your environment; it works fine for me
https://www.oricom.ca/404/
this link is only temporary, sorry :-(
what happens on your end and if it crashes, check the
exact version of Excel!
Excel 2002 10.2614.2625
Check the regional settings, what are they?
In fact, addressing a sheet from another sheet is always
more tricky than addressing it from a module.
Lupin -
Anonymous userHello,
It's my pleasure :-)
I know that if the instruction:
Sheets(FeuilleArch).Range("A4:A65535").End(xlDown).Row + 1
doesn't work well!
make sure that from A1 to A5 the cells are non-empty on the archive sheet and change it to:
Sheets(FeuilleArch).Range("A1:A65535").End(xlDown).Row + 1
Lupin -
Anonymous user
by attempting to modify the line:
Const FeuilleArch = "Archivage"
to:
Const FeuilleArch = "Archive"
the routine crashed exactly at the same point as you!
So I reset the name of the constant by modifying it:
In VBE:
//Menu/Edit/Replace
FeuilleArch
By
FeuilleArchive
Once the name of the constant was replaced throughout the program
I make sure the compiler is correctly referring to the name of the
constant by changing the case of one letter:
ex:
from Const FeuilleArchive = ...
I modify
to Const FeuilleArcHive = ...
In the program, the name should follow automatically without
further intervention.
I spare you the why and how, just know that the compiler (the interpreter) has a memory!
Lupin -
titi14200Hello,
I would like to create a macro for my work that seems similar...
I would like to know if it is possible to create a macro to archive all the data from a row where a cell is assigned a date for an appointment and that date has passed.
Let me explain, I am a craftsman and as part of my job I take my appointments on Excel in a table by assigning a reference, a name, an address, and a phone number for each appointment line, but I don't always have time to go to my appointment, and I would like all my missed appointments to be recorded in a separate sheet so that I can reschedule with the people involved...
Is this possible??? -
titi14200Yes, it is possible to display today's date in Excel without having to enter it manually. You can use the formula =TODAY() to automatically insert the current date into a cell.
Thank you for your responses. -
RenéHello,
I have a table with two sheets and several columns.
In the first sheet, I have a column to put an "x"
and I would like the second sheet to add
the row where I put my "x" without skipping any rows.
How can I do that?
If you want, I can send you an example of the table by email.
See you soon
René