[Excel] Archiving a Data Row.

Question

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

JvDo · Answer

Hello,

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.

Lupin.A · Answer

Hello,

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

Lupin.A · Answer

Hello,

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

Lupin.A · Answer

Hello,

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

Lupin.A · Answer

re :

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

Lupin.A · Answer

Hello,

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

Lupin.A · Answer

Hello,

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

Lupin.A · Answer

re:

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

Lupin.A · Answer

re:

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

Lupin.A · Answer

re:

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

Lupin.A · Answer

Hello,

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

Lupin.A · Answer

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

titi14200 · Answer

Hello,

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???

titi14200 · Answer

Yes, 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é · Answer

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é

[Excel] Archiving a Data Row.

15 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player