Hello, I need a hand here with some VBAs as I'm completely lost.
1)
I needed a vba that transfer documents inserted in an interface in one worksheet to a consultation list split into sections in another worksheet. The document name is the result of 11 fields inserted in the interface and concatenated. The first character on the fields 8 will define in each section the document will go to the consultation list.
2) Once in the consultation list, I needed to create 3 filters (by date, by field 8 type (can be various types) and by field 5 (can be of various types too)) but the filters should bring only the latest version of the document, comparing the 8 first fields, and returning the most recent according to date and (if necessary) the time.
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,987
Re: Transfer and filter docments.
Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish. Forum Rules (updated August 2023): please read them here.
Hello ALIGW,
Thank you very much for the cerity in the answer. I have the excel here but really cannot upload it despite instructions already read.
Please could you explain in baby steps?
Regards,
Hello, I'm just repeating the post posted at the top to facilitate.
I would like some help in:
1) A VBA to transfer documents inserted in an interface in one worksheet to a consultation list split into sections in another worksheet. The document name is the result of 11 fields inserted in the interface and concatenated. The first character on the fields 8 will define in each section the document will go to the consultation list.
2) Once in the consultation list, I needed to create 3 filters (by date, by field 8 type (can be various types) and by field 5 (can be of various types too) being cumulative one to the other(s)) but the filters must compare the first 8 fields of the document name and bring only the latest version submitted. A date field can be useful for this but in the eventuality of having two documents submitted with the same date, then maybe another way (inserting a field with the time?) to filter them.
There is more details in the model. I tried to be as detailed as I could but please let me know if there is anything else I can clarify.
Thank you!!!
Hello guys,
I was able to write a code for the first part of the task (evaluate field 8 and paste it on the sheet LOG) but this is certainly very archaic as I couldn't produce a decent loop for it so I had to copy 4 times the same code (for the 4 Series I have) and will need to produce 9 more codes for all 10 rows in the sheet Input. At least it works.... Or doesn't it?...
The main problem I'm having it here (in addition to the ugly code itself) is that when I try to put all codes together in a CALL module, I don't know why the 2nd module it's not working. If I run them separately, it's ok, though. I tried putting them in the same module and separate (Call) but no joy.
Could anybody tell me why it's not working together or give me a hand on how to write a loop for all the conditions?
Much appreciated!
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,987
Re: Transfer and filter docments.
Thanks for the PM. I have reviewed your post as requested. I am not sure why you are not getting any responses, but I’ll put out a plea for help to see if we can Husarenritt things along for you.
Thank you very much. It works perfectly. There was a mistake I made naming the series but after fixing it and changing the ranges (I had to change a bit due to visual inserts) it works fine. The only thing is that is a bit slow on my PC to process (i7, 16gb RAM), but I can totally live with that.
Let me ask you something very silly but it took me almost a day and I couldn't solve it. Why I couldn't call the separate codes as I had in a master code (please see TIE code)? I revised loads of times but couldn't find a mistake.
Also, is there any hope to achieve the filter functionalities I intend in the worksheet LOG? I'm trying something here at the moment but if I was able to do something in the first part, this is way above my "pay grade"! If you could give me a had here I really appreciate.
Regards,
Hello protonLeah,
In the sheet LOG, where all the documents are stored, I need to filter by series, by field 5 and by field 8 via drop downs.
But I need that only the most recent files (ruled by the date) to be presented. Example: I can have two documents that have exactly the same fields in the name (from fields 1 to 8) but different dates so I need only the most recent one to be filtered. Can I achieve this with VBA?
Good evening,
Please could anybody offer some help. I'm really going crazy with this one!
After several attempts to do the one above, I have changed the purpose a few times to see if I can get this working.
I'm trying to create a document log register in which you input the criteria you need on worksheet INPUT and use the vba Record (from protonLeah below and working perfectly) to record them on the worksheet LOG.
After that. when I use the worksheet INPUT again, I need a vba to compare range NAME (worksheet INPUT T20:T29) with all the files recorded on the worksheet LOG (Range C:C). If the files are not there, the code from protonLeah will run normally and copy them all again but if there is one or more repeated, I would need the repeated ones to be copied over the ones already in the LOG. (erase the old record and then copy the new one).
If somebody could give me a hand here I really appreciate.
I would like to thank o=you once more for the help so far.
I forgot everything about what I've done before and just pasted your two codes together along with a few lines added to obtain the desired result as per below:
PHP Code:
Sub Register()
If Evaluate(WorksheetFunction.CountIf(Range("AS"), "ALREADY SUBMITTED")) <> 0 Then MsgBox "One or more documents already submitted!" Exit Sub End If
Dim LogName As Range, _ TestName As Range, _ InputNames As Range, _ WrkRng As Range
Set InputNames = Sheets("INPUT").Range("T20:T29")
With Sheets("REGISTER") Set WrkRng = .Cells(.Rows.Count, "C").End(xlUp) Set LogName = .Range("C19", WrkRng) End With
For Each TestName In InputNames Set WrkRng = LogName.Find(TestName) If Not WrkRng Is Nothing Then WrkRng.EntireRow.Delete End If Next TestName
Dim SeriesList As Range, _ SeriesNum As Range, _ DestRange As String
Set SeriesList = Sheets("INPUT").Range("L20:L29") For Each SeriesNum In SeriesList DestRange = "S_" & Left(SeriesNum.Value, 1)
With Sheets("REGISTER").Range(DestRange) .Offset(RowOffset:=1).EntireRow.Insert _ Shift:=xlDown .Offset(RowOffset:=2).EntireRow.Copy .Offset(RowOffset:=1).EntireRow.PasteSpecial Application.CutCopyMode = False
Main problem here is: When I have one of the rows empty, the code breaks always on the "With Sheets("REGISTER").Range(DestRange)" line.
Is there a way to test if the row is empty (can be verified using the cells S20:S29 where I have my concatenation) and "do nothing" with the empty row and keep going with the code?
Bookmarks