Gather data that don't exist from different worksheets to another workbook
Hi,
I'm having trouble with a macro aiming at gathering data that don't exist to another existing workbook.
In my example, I have two sheets in the file "Input weeks". In each sheet, I have a list of weeks with a code for every days. If the week already exists in my database, then I get a code, but if it doesn't I want to add the new week created in my database "Catalogue weeks" at the end of the table to a new row.
For instance, in the sheet "Mail 1": the week "J000;D139;JRHE;A041;A043;D137;D137" already exists and the code is E351.
But the week "J001;J002;J003;J004;J005;J006;J007" does not exist and I would need to copy this week to the workbook "Catalogue weeks". Then, in the workbook "Catalogue weeks", I would give a code to this
newly created week and I would need this new code to appear on the sheet "Mail 1".
Last but not least, I can have several sheets named "Mail 1", "Mail 2", "Mail 3"... and I would need to loop accross all sheets.
Re: Gather data that don't exist from different worksheets to another workbook
In column J of Mail 1, Mail 2, Mail 3 …. will there ever be a description which is duplicated among all the sheets?
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Re: Gather data that don't exist from different worksheets to another workbook
Hi dangelor!
Many thanks! It's working really well, I would just need a small adjustement.
In my case, I would need to manually define the code of the new week in the Workbook "Catalogue weeks" and then it would update the name in the sheet "Mail 1" for example.
For example, the code of new week would not be E364 but AJ01.
Re: Gather data that don't exist from different worksheets to another workbook
Is this possible thanks to a VLOOKUP for instance? Ideally, I would need that once the code is entered on the Catalogue Weeks, the sheet "Mail 1" gets updated automatically.
Re: Gather data that don't exist from different worksheets to another workbook
If you are willing to let the macro generate the codes automatically and sequentially, try this macro in the Input_Weeks workbook. You will have to run it only once.
According to the attachment as a VBA beginner starter to paste to the inputs_weeks workbook
(once the catalogue 'database' is updated run it again) :
PHP Code:
Sub Demo1()
Const S = "Catalogue", D = S & "_weeks.xlsx"
Dim V, Ls As ListObject, Ws As Worksheet, Rg As Range
V = Evaluate("ISREF('[" & D & "]" & S & "'!A1)"): V = IIf(IsError(V), False, V): If Not V Then Beep: Exit Sub
If Workbooks(D).Worksheets(S).ListObjects.Count = 0 Then Beep: Exit Sub
Set Ls = Workbooks(D).Worksheets(S).ListObjects(1)
Application.ScreenUpdating = False
For Each Ws In ThisWorkbook.Worksheets
With Ws.Range("C4:I" & Ws.[C3].End(xlDown).Row)
.Columns(9).Replace "?", Empty, xlWhole
Set Rg = .Columns(9).Find(Empty, , xlValues, xlWhole)
While Not Rg Is Nothing
V = Application.Match(Rg(1, 0), Ls.ListColumns(2).DataBodyRange, 0)
If IsError(V) Then
Ls.ListRows.Add: V = Ls.ListRows.Count
Ls.ListRows(V).Range.Columns("A:B") = Array("?", Rg(1, 0))
Ls.ListRows(V).Range.Columns("C:I") = .Rows(Rg.Row - 3).Value2
End If
Rg = Ls.ListRows(V).Range(1).Value2
Set Rg = .Columns(9).FindNext(Rg)
Wend
End With
Next
Application.ScreenUpdating = True
Set Ls = Nothing
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Re: Gather data that don't exist from different worksheets to another workbook
Hi Mumps and Marc,
Many thanks for your answers but I'll go with the solution provided by dangelor because it suits my needs and with my knowledge in VBA, I understand it better.
Re: Gather data that don't exist from different worksheets to another workbook
Dangelor,
If I may, in my original file the catalogue actually starts at column B, could you please indicate me which line do I need to change?
I would also need another tweak if possible. Once my weeks are created, I need to transpose the data to another sheet and create a new code (I have updated both examples accordingly).
For example, in the sheet "Mail 1", the working cycle created is AA01 (it comes from the tab "Work cycle"). Could you please help me with that?
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by dangelor
Hi ! Try this demonstration !
Bookmarks