Excel macro to create workbooks and synchronize data (and more) [Closed]

Registration date
Wednesday June 29, 2016
Last seen
June 29, 2016
Hi there,

A little more than a year ago I made a commitment to go in-depth with Excel. Since then I have been practicing 4-8 hours every week, and now I am working on my first non-personal project, an Excel-based administrative system for the company I work at. It all went really well and the project is almost completed, the only thing left is writing the macro. Oh wait, the macro is technically the project itself, the rest is worthless without it…

Before I started to work on the project I looked up what kind of VBA code I will need and how difficult it looks. It looked simple and very straight forward, so I thought the coding part will be the easy one, just need to look up some sources.

Seems like I failed to realize a few things at the time:
1. The code I will need is actually quite a bit more complex than what I've looked at.

2. Code is not Lego. I can't just simply piece mine together from others'.

3. There is no such thing as "good enough" or "OK" code, and it can't be "close enough" either.

I underestimated the task and right now it seems almost impossible to come up with a working macro on my own. I've decided to post the details here, hoping someone can help me out. Below I list everything there is to know about the workbooks that are part of the system. I also provide some context and share everything that might be relevant to the code. There are some details that I don't find crucial, only interesting and somewhat helpful. I put those in italic, to save you some time,

Any help would be highly appreciated And it is not just a figure of speech, but rather an understatement. I am desperate here…. I subscribed to Lynda.com and finished a course on macros, but I can’t say this got me any closer to a working solution for my problem.

Some context: company and project overview

We are located in Budapest, Hungary and provide property management services. More specifically, the company is specialized in the management of short-term rentals on Airbnb, which essentially means that we create and manage Airbnb apartment profiles for our clients, and take care of everything associated with their rental. As reviews and ratings are everything on Airbnb, the most important aspect of our service is customer service. Therefore, we personally welcome guests upon their arrival, show them around the apartment, give them a map, mark the apartment's location and provide an overview of the city, answer their questions, etc. This is what I do at the company, along with three other employees.

On our end, providing the service described above boils down to a surprisingly large amount of information. The project aims to manage this information and automatically get the relevant details of incoming reservations to the employees who need them, and provide a comprehensive overview for the manager at the same time.

The macro (through an example)
There is a workbook called 'name_of_month.xlsx', its path is 'X/Master/name_of_month.xlsx'. In C3 (of a specific worksheet), the value 'Brandon' is selected from a dropdown list. When the macro runs:

1. If the path 'X/Brandon' does not exist, create it.

2. If the workbook 'X/Brandon/name_of_month_Brandon.xlsx' does not exist, create it based on X/Templates/schedule.xlsx

3. Copy the value of 'X/Master/name_of_month.xlsx' A3 and B3 to 'X/Brandon/name_of_month_Brandon.xlsx' B2 and C2, respectively.

4. Copy the value of 'X/Brandon/name_of_month_Brandon.xlsx' E2 to 'X/Master/name_of_month.xlsx' K3.

I'm not sure that the example above does completely covers what I need the macro to do exactly, so here's some clarification:

• Each line in the master workbook corresponds to an individual reservation. Some pre-determined details (columns) should be copied over to Brandon's workbook, others shouldn't. The order of the copied columns in the source workbook does not match the order of columns in the target workbook.

• Some details are going to be input by Brandon and should be copied back to appropriate column of the master workbook, in the line that corresponds to the particular reservation.

• Changes in copied values have to be kept in sync even after they were synced initially. (But only in the source, values in the target document cannot be edited. This should simplify things.)

• There will be an individual master workbook for each month. Brandon's workbooks should be named in line with the name of their source (e.g.: the name of the month). The name of the actual month could be input in a cell of each master workbook, too, if that is simpler.

I am not even sure that I would be able to achieve the desired results with the approach I described in my example... Also, I am not a native English speaker. I did my best to explain everything as well as possible, but sometimes struggled to find the appropriate expressions. I hope It's not too clumsy and you guys will be able to understand it. If there is anything that needs clarification, please let me know and I will provide an explanation ASAP.

Please, please help me out with this! I have been at it for way too long, and they count on my system to work. Failure is not an option here :(

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!