Deleting Folders Within A Macro?

Aug 23, 2012

So I have a macro that creates a monthly set of reports, but some of them only relate to period 5 onwards. I wanted the macro to check whether a folder exists for periods 1 to 4, and if so, delete the folder and all the files inside it. I've used the following...

If Period < 5 Then
If Dir("I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast") "" Then Kill "I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast[code]....

but the macro just skips straight through the secondary IF statements without stopping.

I know this works on deleting files, and presumed it did too for folders, but it doesn;t.

View 2 Replies


Making Macro That Will Create New Folders And Then Save In Those Folders?

May 21, 2014

I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.

As an brief example.

Cell "B3" on the master(worksheet) contains a job name I-40 bridge
So initially I would like the Folder name and file name to be I-40 Bridge

However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.

I am currently working with Excel 2007 Pro edition on windows 8. I

I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.

Public Sub SaveAsA1()
ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub

View 4 Replies View Related

Use Excel VBA To Copy Multiple Files From Different Source Folders To Different Folders?

Jun 12, 2014

I need to back up files, which may be excel or MSword, by copying them from different directories, say C: to the backup directory, say X:. any vba to backup the files? I expect the vba can copy all file listed in column A.

E.g. C: est1File1.xls or C: est2File2.doc, and then pasted to the directory in column B. e.g. X:BackupFolder1 or X:BackupFolder2.

View 1 Replies View Related

Arrange Or Group Folders Based On Assigned Icons To Folders Using VBA

Dec 20, 2011

I have folders with different assigned folder icons based on the content in the folders.

I would like to arrange these folders in the window based on the assigned folder icons and then by alphabetical order.

How it is to be done. Can we make use of vba having the folders path list in one column of excel sheet?

View 9 Replies View Related

Search Folders And Sub Folders For Worksheet And Return Range?

Jul 8, 2013

macro and I think this has me on my way; however, I do not understand the code and I am having difficulty modifying it. It returns the path in cell $A1 of my workbook, but I get reference errors in $B1:$L1.

I would like for the code to start entering the values in row 2 of my current worksheet. Row 1 I want to make a header row. The first column I like having the file path. Can the path be changed to a hyperlink? I would then like for it to return the values in $AP1:$BC1 to $B1:$O1 of my current workbook.

View 4 Replies View Related

Consolidate Data From Multiple Files Within Folders & Sub Folders

Aug 16, 2009

I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.

The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.

I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.

Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).

Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?

Sub Click2()

Application. ScreenUpdating = True
Dim MCDrow As Long
'Dim SubFolders As String
MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row

Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory
'SubFolders = True
Fname = Dir(Fpath & "*.xls")
Do While Fname <> ""

ThisWorkbook.Sheets("Client Data").Unprotect

Workbooks.Open Fpath & Fname
Worksheets("Client Data").Activate
Worksheets("Client Data").Unprotect

View 6 Replies View Related

Run Macro In Sub-folders

Dec 25, 2010

I have created a macro named "SummaryCalculation" and I want to run this macro to more than 100 excel files which are saved in subfolders in a main folder. Each subfolders have atleast one excel file. I want the macro to be run with in these subfolders not the files are in main folder.

View 3 Replies View Related

Loop Through Folders And Create Folders?

Mar 31, 2014

I want to open C:Projects

and for each Project in Projects, create a new folder with the actual month (e.g. "03" or "12")

Im running the Makro from a file where I'd like to put the month in a cell -> so the code refers to cell("A1") wich has value 01 and creates a folder "01" in every projectfolder in C:Projects

View 3 Replies View Related

Dir Folders And Subfolders Macro Writes Over It Self

Sep 14, 2009

I have a code that will write all filenames in a selected folder and all subfolders in a list. The problem is that when i open a folder with about 5000 files in only 187 are visible in the list. You could see that the program writes all filenames but it writes everyting in the first 187 columns over and over again. Here is the

View 5 Replies View Related

Create Macro To Rename Worksheets In Other Folders

Dec 23, 2008

I have created an appointment schedule spreadsheet. Once I get the spreadsheet running smoothly, I would like to create a worksheet for every day of the year that we are open.

I have decided to have one main folder with 26 subfolders in it. In each of those 26 subfolders, there will be one workbook with 12 worksheets in it. That will be two weeks worth of appointments as we are open Mon-Sat. Of course I want to name the worksheet tabs at the bottom of the workbook according to the appropriate calendar date. Then there is also a cell at the top of each page that also has the date, the same as the date on the tab. Just wondering if there is a simple way to create a macro to rename all these worksheet tabs, or if I have to physically open up each workbook, and rename all the worksheet tabs according to the calendar date. Then once the worksheet tab is named, can you make it automatically put the same date into Cell A1?

View 9 Replies View Related

Macro That Cycles Through Sub Folders Within A Main Fold

Sep 15, 2008

i am looking to create a macro that cycles through sub folders within a main fold and takes the same line of nformation from each workbook that it cintains.

the main folder is a yearly folder "2008" there a 12 subfolders within this named "january" to "December" (one for each of the months of the year) Within the monthly folder are four weekly forlders named "week 1" to "week 4"

each of the line in the workbook ferer to a different person so i will need to transfer all of them into individual summary workbooks. but in this example i need to take the information from A3 - K3 from each workbook.

View 9 Replies View Related

Macro To Label Folders With Tags And Filter As Required?

Jan 29, 2014

Basically I have a large database of folders of examples of projects/notes etc, each with essentially lots of potential topic "tags". I want to sort them to be able to tell the macro what topic I am looking for and it give me the folders that contain that info. I will need to tag each folder individually (which is no problem). I am looking for a piece of script (that either exists or that I can write) that will do the following:

- tag specific folders with numerous "tags". The folders will all be stored in a database. The tags will be user input.

- sort these folders based on specified tags and show filepath/location/name/file number or something unique based on the tags input.

View 14 Replies View Related

VBA Macro To Loop Through Folders And Select Files With Certain Criteria

Feb 6, 2014

I'm looking to create a macro that does the following:

- Loops through a folder structure
- Opens a specific subfolder within each folder based on name (i.e. "*Financials*")
- Opens a specific file within that subfolder "*Financials*" based on both most recently modified a naming criteria (i.e. "*Model*")
- Performs an action on that file (a macro created elsewhere that I assume I can call here)
- Closes that file, and moves on to the next file matching the criteria above

As a self-diagnosed VBA novice, I'm having trouble adapting code found in various forums for my specific project.

I've found examples that address aspects of my desired macro or do similar things, but I can't figure out how to put them together.

Open most recent file:

[Code] .....

Loop through folders and subfolders and print all files:

[Code] ....

View 1 Replies View Related

Excel 2010 :: VBA Macro To Synchronize Windows Folders

Apr 19, 2013

Working with Windows 7 and Office 2010

I've created a Excel workbook with VBA code to synchronize an Access database between a laptop and a server. One of the fields in the database is a shortcut to a jpg for each record. (The Access stuff is not too important here)

Now I'd like to add the ability synchronize the photos between the laptop and the server; so people who use the server can view the photos added by the laptop and vice versa.

Something like:

For each [JPG] in [LaptopFolder]
If not [JPG] exists in [ServerFolder] then
filecopy [LaptopFolder][JPG], [ServerFolder][JPG]
end if
next [JPG]

That code wouldn't work.

View 4 Replies View Related

Using Macro To Print Various Word And Excel Files In Different Folders

Jun 2, 2014

What I want to create is an excel file such as:




Where Qty sets how many copies for that particular doc, and Yes/No determines if it needs to be printed at all.

Each file will be in a subfolder relative to the excel file.

So it would work like this (somehow)

If Cell, C2 = 1, then print CurrDirSection1examplefile.xlsx, qty= B2 , defaultprinter
If Cell, C3 = 1, then print CurrDirSection2examplefile.docx, qty= B3 , defaultprinter
If Cell, C4 = 1, then print CurrDirSection2examplefile.docx, qty= B4 , defaultprinter

and so forth.

View 3 Replies View Related

Macro To Open Specific File By Searching Multiple Folders

Oct 11, 2013

I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.

The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".

I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.

So far I have the following:

Sub Open_ESCL_Report()

Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub

Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx

View 3 Replies View Related

Macro- Code To Transfer Data From Multiple Workbooks Within Folders

Sep 15, 2008

I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.

the folders are set up as follows,

there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.

for example a1 - k1

the code i am using transfers the file names but comes up with #REF! instead of transfering the data

Here is the

View 9 Replies View Related

Create Macro That Will List All Folders / Subfolders And Files In Order In Excel?

Feb 8, 2013

My problem is to create a macro that will list all folders, subfolders and files in an order in excel. So that I know which folders and files belong to which main folder, more like a hierarchy.

For example,
Subfolder 1

I have code that list all files in folders and subfolders, but it does not put them in a hierarchy fashion.

View 8 Replies View Related

Macro To Copy Folders To Path Of Open Workbook Based On Cell Values

Aug 3, 2014

I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.

C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365

I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....


Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)


This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.

View 2 Replies View Related

Deleting Worksheets With Macro?

May 2, 2014

I am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.

View 2 Replies View Related

Deleting Rows With Macro

Mar 19, 2009

I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?

View 2 Replies View Related

Deleting All Sheets Except One (macro)

May 8, 2012

I need a simple macro to delete all sheets in a workbook except one that is called "START"

View 9 Replies View Related

Deleting Rows Using A Macro

Jul 25, 2012

I'm looking for a macro that will delete a row with a certain value (0.00) and the row below it.

View 9 Replies View Related

Setting Up Deleting Macro

Oct 19, 2013

I'm trying to delete both rows of the same data not just one.






What i need is if there is a same location delete both rows and i cant figure it out to get it to do both of them not just one row.

View 9 Replies View Related

Deleting With For Loop (Macro)

Jan 2, 2014

I have a problem with the following macro:

Sub CalcsDelete()

Dim count As Integer
count = 1

For count = 1 To 100

ActiveSheet.Shapes.Range(Array("Picture " & count)).Select

count = count + 1

Next count

End Sub
It should simply delete picture 1, picture 2, picture 3, picture 4, etc. The problem lies within the "Picture " & count part.

This is obviously the name of the picture, i.e. picture 1, picture 2, etc.

That's what I tried to accomplish with the for-loop, but I'm not sure how to increment the number of the picture by means of using a for-loop and having that be the new name for the picture.

View 6 Replies View Related

Deleting Macro Buttong After Use

May 20, 2008

how to remove a macro button from the sheet after we've used its functions.

For example, i have a macro that emails the workbook. Once the emailed workbook is out, i would like the macro button to be removed after we press it.

View 9 Replies View Related

Deleting Rows With A Macro

Jul 28, 2007

I am looking to find and delete email addresses in this case * I have only one column which is full of email addresses, I just want to delete all of the ones in this case. This is the macro I am trying to use, but it does not work.

For Each cell In Range(Range("a2"), Range("a65536").End(xlUp))
If cell = "*@aol" Then
Range(cell, Cells(1, Rows.Count)).EntireRow.Delete
Exit For
End If
Next cell

View 3 Replies View Related

Deleting / Disabling Macro Once Macro Run?

Feb 18, 2014

I have a couple of macros in the book that plug through a list of "stores", input the store number in a cell, then copy the results to a new workbook, value out the formulas, and save it, before moving onto the next store. There are also 2 types of stores, "STORES" and "CORPORATE", and the sheet toggles some things based on this input. One of those things is a "ON CHANGE" macro that hides and unhides rows appropriately. Everything works in my Master sheet, but the problem is that once I copy the page (and the macro embedded) to a new workbook, I save to a lower version of Excel and it all gets hung up on the embedded macro.

Is there a way to disable/delete the macro once it goes to the new book? I've tried "Application.EnableEvents = False" and i think this is the right route, but i think maybe i'm placing it in the wrong place(s).

Here;s my code:

' PY12 Macro


View 3 Replies View Related

Speed Up Macro Deleting Every Nth Line

Jul 4, 2012

I am dealing with data sets from various instruments that have different sample rates. I am deleting data points I don't need from some of the sets with higher sample rates so that all the data is on the same time scale.

The macro I have is super simple, but incredibly slow. I'm simply deleting every other cell down a column.


Sub OATcondense()
Application.ScreenUpdating = False
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Select
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Macro For Deleting Whole Row Even If Single Data Is Not Available

Apr 2, 2014

I want a macro which can detect empty cell in rows and can delete whole row in which that empty cell is .and want to shift rows up. Macro should start searching empty cells from Column B meaning that It should not delete cells in Column A.

And I want this function in all sheets except parent sheet at position 1.

File is attached : inquiry 3.xlsx‎

View 6 Replies View Related

Copyrights 2005-15, All rights reserved