Need help for VBA code - Date selection

[pmchris]

Hi;

I need to copy last work day's data into my current worksheet. Last work day's data can be found at K:\Downloads_Uploads\Downloads\Daily\GV8 downloads\2015\December\1214.xlsx. I know the code that open the file, copy and paste the data, but the difficulties I met are that how can I define the variables and create the path as year, month and date changes. (for example, today is Dec 15, so I need to open file from K:\Downloads_Uploads\Downloads\Daily\GV8 downloads\2015\December\1214.xlsx, but if today is Monday, Dec 21, then I need to open file K:\Downloads_Uploads\Downloads\Daily\GV8 downloads\2015\December\1218.xlsx).

I appreciate if anyone can give me a help.

Chris

[alansidman]

How about creating variables for year, month and day and have them created using Input Boxes? Then substitute the variable for the hard coded year, month and date in your path.

[LJMetzger]

Hi pmchris,

See the following two macros for two slightly different alteratives.
a.The first creates a 'Target String' and replaces tokens with the appropriate Year, Month, and Day of Month components.
b. The second adds Year, Month, and Day of Month components to a 'Base String'.

Please Login or Register  to view this content.

Lewis

[pmchris]

Hi Lewis/Alan;

I really appreciate you create these codes/suggestions for me. Since I am a beginner for using VBA code, there are some sentences or definitions I don't understand in your codes. For what I need, my thought is I want to create two dates in my current worksheet (for example, Cell A1 is today's date and Cell B1 is Last_work_day's date (no matter last work day is Friday, or any workday I worked on)), and then when I look for last work day's file in the folder, I can create the path by applying the variable I defined in Cell B1. At the end of code, I copy the Cell A1, and paste special value to Cell B1, so the Cell B1 is always the last work day's date. Does it make sense to create code as what I thought?

I don't know how to define and apply variable in Cell B1.

Chris

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[pmchris]

Hi John;

Your code works great. Thanks for help. Have a happy holiday.

Chris

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please refer to the link below under General Suggestions and mark this thread as SOLVED.

http://www.excelforum.com/forum-rule...rum-rules.html