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.