Forum Discussion
VBA Nested Looping Structure
Need help as i'm getting up to speed on VBA for Excel. Took most of Leila Gharani's VBA course and found it incredibly helpful but of course lots to learn before I have enough knowledge base locked away to be dangerous. I've got several chunks of what i want to accomplish working, but combining all of them is the challenge - hoping someone can steer me in the right direction as far as general structure for nesting loops in VBA.
Scenario - two supervisors who report to me need to assign downtime to their teams. Currently we do this via manual copy and paste from a master spreadsheet to each team members individual sheet. The members can also add their own specific downtime to their sheets. Supervisors add team wide downtime for consistency/fairness sake, to everyone's tabs.
I have a macro built from the master workbook that opens all team workbooks via FSO path. Another that identifies the range of data from the master workbook table and copies it. It's the next part that is failing my skills: I need to paste the same information to everyone's sheets. Should the structure be one of the following flows or something different entirely?
1. Open master file, open all recipient files. Select and Copy Master Data. Paste in Recipient 1, save, close. Activate Recipient 2, paste, save, close. Loop through remaining files.
-or-
2. Open master file, select/copy master data. Open Recipient 1, paste, save, close. Open Recipient 2, past, save, close. Loop through remaining files.
Do i need to copy from the master between each paste operation, or will the copied selection live in VBA memory until the macro ends/tells VBA to clear the copy (for lack of a better phrase)?
Note - I wouldn't want to do this by VBA, except for the fact there are currently 25 team members (and the team is growing soon), and doing the reconciliation is bigger waste of time than it should be. Also, this will reduce email traffic from folks sending logs of downtime back and forth.
Any advice is helpful.
Thanks
Joe
5 Replies
- I'd go for option 2. You'll see soon enough whether you need to redo the copy for each file or not 🙂
Can't you use Get & Transform to retrieve the data from that master workbook into each persons workbook rather than using VBA?- never yet encountered "Get & Transform" /Power Query. From the online help documentation, "Get" is used to import from multiple sources. My goal is to send to multiple sources from a single source. Unless I'm missing something, it doesn't appear this would be a solution for me? My users are not tech savvy, and my goal is to do a single action to send the info out, as opposed to multiple actions - one from within each receiving workbook - as the get function might appear to work in the suggestion? That's a lot of books for a supervisor to open and then manually kick off a get function - doesn't seem much faster than a copy/paste. Again if i'm misisng something in get/transform please let me know - i'm game for a simple, single click solution with repeatable, reliable (relatively error-proofed) outcomes.
- In your case, you would create a data connection in each client file that pulls in the required data, so instead of pushing data, you're pulling data.
