Hi this is my very first post / question and I'm a complete novice to VBA so my apologies up-front if I miss anything or get it wrong.
I want to create a parent spread-sheet called "Master" and say, 10 separate "Client" specific files.
All files will be locked and protected to prevent changes to the structure / format.
The Master file will have a summary "Front Page" with 3 columns of data in cells B2:D100.
Each Client file will have the same summary "Front Page" with the exact same cell range.
All the summary "Front Pages" will be in the exact same format and the only difference is that in the Master I want to bring in the individual Client summaries and post them as side-by-side columns: ie. Client 1 into B2:D100, Client 2 into E2:G100, Client 3 into H2:J100 ....
To do this with a measure of control what I want is a piece of vba code within the Master file that will:
- Prompt the user via a File Open Dialog box to select a specific Client file
- Go automatically to the Client file's summary "Front Page" worksheet and copy a pre-defined range of cells (always say, B2:D100) in terms of their contained value and format (formulae are not required)
- Prompt the user to select a Client (1, 2, 3, 4 .... 10)
- Paste the copied range values and formats into a fixed range within the Master file according to the selected Client
- Prompt the user with the question "More Client files to import?"
- Exit if user selected "No" or prompt user with File Open Dialog Box if user selects "Yes"
Any help on this would be great.
I've searched and googled this and found various elements of what I need but nothing so far that works completely