EXCEL VBA VLookUp

Hello to everybody,


my name is Carl and I'm new to this community. To my knowedge in Excel: very good skills. But I'm new to coding in VBA, because before my new job it was'nt necessary, because I didn't have to do repeating works which can be automated with VBA.


So now I'm trying to write first codes.


Ok, let's come to my problem.


I have a workbook with actually two worksheets in it. In each of the worksheets is an identical table. The tables contain customer orders and the wished delivery date from customer and the estimated delivery date from us, and some other colums, which are not nessecary for my problem.


I would like to create workbook week by by week for the customer orders. So at the moment the worksheet(2) ist called CW26, and the worksheet(1) is called CW27. In CW 28 i will ad new worksheet before the worksheet CW27 called CW 28, and so on. In CW 27 i would like the estimated delivery date to customer of the week 26, in week 28 the date of week 27....So i can the changes of the estimadet delivery date week by week


So the postion of the new worksheet for the actuall week always will be worksheet(1), the position of the week before always will be the worksheet(2).


Only the name of the worksheets and the tables will change.


I'm already getting copied the worksheet of the new week before the old week. My problem is the function for the look up. Because the name of the worksheet and the table is alwasy chaning, i had the idea to use variables for the name of the two worksheets.




Here my first tries:


Set WeekBefore = Worksheets(2).ListObjects(1) -
Set ActualWeek = Worksheets(1).ListObjects(1)
Worksheets(1).Select --> select the worksheet for the actual week
ActiveSheet.ListObjects(1).ListColumns(10).DataBodyRange.Select --> select the the coloum for the input of the estimated delivery date of the week before

Selection.Value = Application.WorksheetFunction.VLookup(Worksheet(1)ListObjects(1)ListColumns (1), WeekBefore, 8, False)






This code leads to the error message '1004'. How I have to change the codes, so that I can set the values of the first colums (cumstomer oders no.) as arg 1, the table weekBefore as arg 8, the coloum 8 of the table week before as arg3.


I hope you can help me. Thanks in advance.

Carl, welcome to the forum. Can you clear a few things up for us?

Quote:

---

I would like to create a workbook week by week

---

At first reading, are you suggesting that you want to create a new workbook ( consisting of two worksheets) every week? Surely this becomes unmanageable after a very short time. 52 workbooks a year, 104 sheets to link. Would it not be better to simply have one workbook with two or more sheets, containing Customers, Orders, Wished delivery date, Estimated delivery and Actual delivery data. This sort of stuff is appropriate for a DB application rather than Excel.

The error message "1004" is excel trying to tell you it is confused about where to find data ranges that you think you are referring to.

Is it possible for you to attach a sample workbook with some dummy data so we can see what it is that you are wishing to see actioned?

Hello Aussiebear,

thanks for your fast replay. I made a mistake in my despription. As you already suggestet I mean a workbook with a new worksheet week by week. Sorry for this mistake.

In the attachment I uploaded a data dummy. In this dummy I insert the formula I want to create with VBA directly to Excel by using the VLookUp Formula in Excel. I hope it is understandable what I want to do. As you can see, the name of the table is changing week by week.

The first Marko is for inserting the new worksheet on positon 1, with the second I'm trying to generate the VLookUp using VBA for the value in Cell "A2"

Once again I must stress that this concept is more suited to a database application rather than Excel. I can foresee a rather bulky workbook that is hard to manage. Why couldn't the Week value simply be a value in a column, all on the same sheet?

Hello Aussiebear, I would like to see and compare the differnce between the estimated delivery date in the acutal week, and the week before. If there is a change in date I would like to take the list with these oders send it to all departments involved like purchaising, assembly, warehouse, and everybody has to enter a countermeasure for the department. If I'm using a sheet with all the dates I think i can't the these shifts week by week. If there is a other possibillity to do this in excel i woud be very thankful for ideas.