Using VBA to create folders with preloaded spreadsheets

rachel06

New Member
Joined
Feb 3, 2016
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Hello,

I currently have a macro that I run to create quarterly folders needed for payment processing that's pretty straight forward. Each month, we use a few different spreadsheets to do analysis, and I've been manually placing them in all of the folders after they are created. Is there something I could build into the macro that would create the folders and contain the spreadsheets we need for analysis? The spreadsheets are never renamed and are always in the same folder on the network.

Thank you! :)
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,705
Are you asking :

#1 : Create new folders on your computer? Such as: C:\Test or C:\Users\YourComputerName\Desktop\MyNewFolder ?

#2 : If the above is correct ... what are the folder names ?

#3 : Where should these folders be created ?

#4 : How many workbooks should be created ? One for each month of the year ?

#5 : What name should each of these workbooks be given ? One for each month, i.e, Jan ... Feb ... Mar ... etc ?

#6 : Are you wanting a WorkSheet or WorkSheets named something specific ? If so, what ?
 

rachel06

New Member
Joined
Feb 3, 2016
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Thanks for the response! The macro I use already names the folders and places them on the network drive where they need to go. That part is taken care of. I'm wondering that, in this process of creating the folders, if a copy of existing workbooks can be in those folders after created. Like is there a way to tell the macro to grab a copy of the workbook from a specific folder/location on the network and place it in the newly created folders?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,705
Hmmm .... I've never worked with a NETWORK environment. If the common SAVE command works ...

Are all the workbooks currently residing in the same folder ... and no other workbooks are stored there ?

What network folder should the workbooks be copied to ?
 

rachel06

New Member
Joined
Feb 3, 2016
Messages
47
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I could put all the workbooks I want to move in one folder with nothing else stored there. We create a new folder for each quarter, and with in that folder are more folders for each analysts groups of clients (IE the folder would be called 2020Q1 payments and within there are the folders where I'm wanting the workbooks moved to). Both the workbooks I'd like to move and the folders I'm creating are within the same folder.

A little backstory for more clarity: We create the new quarterly folder manually each month. Then we have a workbook to create the new client folders within that. Basically, in one cell we enter the destination folder for the new folders to be created, then there's a list of names for the analyst folders below that. So the macro is making empty folders and placing them where we indicate in one cell and naming them what we indicate in others.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,705
Here is a project that copies files from one folder to another.

The folder names are Test1 and Test2. The project first clears out Test2, then
proceeds to copy files from Test1 to Test2.

You've indicated you already have a working project that creates the folders and
files for you. So ... if you were to place those in folder Test1 ... they would
auto copy to Test2.

Now ... the macro code can be modified to change the folder names and locations so
it coincides with your existing paths there on the Network.

Your thoughts ?

Download file : Testing.zip
 

rachel06

New Member
Joined
Feb 3, 2016
Messages
47
Office Version
  1. 365
Platform
  1. Windows
I'll play around with this and let you know what I come up with.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,600
Members
410,625
Latest member
Hemantjain86
Top