Excel :: Create New Folders In Variable Location

Create Macro To Rename Worksheets In Other Folders
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 Replies! View Related

Create Folders & Copy Files To It
I need to create a macro that will allow the user to create a directory based on a template directory, with all directory paths/ names stored in workbook cells:

1) Create a new directory (name/path specified in workbook)
2) Go to a template directory & copy the entire directory, subdirectories & all files
3) Paste them into the new directory
4) Save a copy of the workbook into the specified location from (3)

Example:
X:Templates
-copy all to-
X:Projects(Value specified in workbook cell)

View Replies! View Related

Consolidate Data From Multiple Files Within Folders & Sub Folders
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 Replies! View Related

Insert Row At Variable Location
I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes

A
A
A
A
A
B
B
B
B
B

and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!

View Replies! View Related

Store Cell Location In Variable
way to store a cell's location to variables.

Something like:

int a, b
Cell(a, b) = ActiveCell

I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.

View Replies! View Related

Create A New Table That Displays The Information By Location Instead Of Code
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.

I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.

I'm hoping the example will make this clearer.

On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.

To make this more complex, I can't use vba on this one.

the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.

something like this.

(edit changing 1:1 to $1:$1)

=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))

It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!

View Replies! View Related

Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.

Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?

The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.

View Replies! View Related

Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.

Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

View Replies! View Related

Create A Constant Variable
I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.

i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.

I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"

View Replies! View Related

Create Rank Base On 2 Variable
I hv following table :-

Summary Report FGH1how urgent How ImptRank2highHigh 3midhigh 4LowHigh 5highmid 6Midmid 7Lowmid 8Highlow 9midlow 10lowlow Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4

I need code , when run it will fill in the ranking number :-

Summary Report FGH1how urgent How ImptRank2highHigh13midhigh24LowHigh35highmid46Midmid57Lowmid68Highlow79midlow810lowlow9Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4

View Replies! View Related

Create A Range Variable From Cell Text
Let's say I have cell A1 on Sheet 1 of Book 1. I need this cell to be a range variable for a macro in Book Two, but cell A1 varies from time to time (A1 this time, A7 next time).

In Book Two, I write "A1" as the contents of cell C3, for example. How do I turn that string into a range variable for use, not the containing cell? Since the cell needs to manually changed each time I run the macro, having it as a text field somewhere on my Book 2 Sheet seems the only simple way to identify it but IS there another way? Selecting it as the active cell and using another subroutine defeats the use of the macro.

View Replies! View Related

Files Within Sub Folders
I am using the code below to list all files within a specific folder however I need to know about ALL the files within the underlying sub folders (of which there are many).

I have tried wildcards when declaring the folder but it does not work.

Sub List_Files()

Dim fsoObj As Object 'Scripting.FileSystemObject
Dim fsoMapp As Object 'Scripting.Folder
Dim fsoFil As Object 'Scripting.File
Dim sFolder As String
Dim i As Long
Dim filtype As String
sFolder = "V:T-ASSETAsset ScotlandNetwork Capacity Section1 Business Plan"

Set fsoObj = CreateObject("Scripting.FileSystemObject")

'Set fsoObj = New Scripting.FileSystemObject........................

View Replies! View Related

Opening All Folders
I have about 20 plus workbooks in a folder. Each has 52 sheets, Fridays date, that I want to copy all the data from each sheet.

Is there a way to open all workbooks in a folder and copy all data from on all the various sheets into a single workbook?

I'm trying to gather the info and make a small data base from the various books. I'd like not to have to call each workbook by name as the file names change or get added to.

View Replies! View Related

List Folders To File
Does anyone have a macro that would list all the folders (with path) and
subfolders to an excel sheet but not the files?

View Replies! View Related

Creating Folders From Spreadsheet?
I need some help, I know very little about macros but is it possible to create folders/directories to a certain path using the text in an excel spreadsheet cell? For example, if cell a2 has a street address and cell b2 has a city, can it automatically create a directory in the path of my choice using that data? If yes, can it go down the the rows and create directories until it is done?

Please help as I have hundreds of directories to create.

View Replies! View Related

Open Workbooks In Two Folders
I have a script below that opens all the workbooks in a folder based on a msgbox. The script works as is. However what I would like to do is remove the msgbox and have the script go back and forth between the folders.

Example it would open one workbook in the (Large Area)folder call my other script (Hazleton_Data_Conversion) Return Open a workbook in (Varsity) Folder Call the
my (Hazleton_Data_Conversion)and repeat the process till all the workbooks in both folders have been processed.

Like I said this script works with the msgbox I just like to change to without msgbox and to flip back and forth.

View Replies! View Related

Listing Folders With VBA
I have found a lot of posts which show me how to list a directory tree in an Excel file, usually using a macro. I'm just uninitiated with VBA / VBS and simply don't have the time to figure it out now (it really isn't part of my job description... I'm rather doing a favor here).

I need to be able to re-generate an excel-based list of clients at will. The client names are subdirectories of different case types. There aren't tons of case types, right now maybe 20 but I need to be able to add case types without breaking the script.

case_typeslast,_first_name........

View Replies! View Related

Finding Files In Folders
I need to design code that can check to make sure a specific file is found before it executes.

Our files are all going to start with the same words followed by the date

ex. Name 10-2-1998

I want the code to be dynamic and be able to check in the folder that the current excel sheet is in for the file i need and then have it ready for use in my macro.

View Replies! View Related

List Folders With Certain Names
way of returning a list of folders that contain a user defined search string in their name. Eg., return all the folders within a directory/directories that have "x365" in the name.

I am searching through a list of over 6000 folders that are sitting in four different directories.

The following is the code i've come up with so far that creates an array called aFoundList containing all the folders that match my search string.

Sub TryOpenFolder_LoopingMethod()
Dim FSO As Scripting.FileSystemObject
Dim oFolder As Scripting.Folder
Dim vSubDir As Variant
Dim vSearchDir As Variant

View Replies! View Related

List Files And Folders
I have a folder that has a bunch of other folders in it, each containing many files. Is there an easy way to get a file directory into excel, so that I now would have a spreadsheet that tells me what all the folders are and what files are in each one?

short example: main folder = mystuff
subfolder1 = my pix, contains pic1 and pic2
subfolder2 = otherstuff, contains otherstuff1 and otherstuff2

Can excel find these things and give me a list indicating that the 'mystuff' folder contains these two folders, and also list the two files under each folder?

View Replies! View Related

Fill Listbox With Array Of Folders
(Modifying some code by Leith Ross) I am stumbling why I get an error trying to fill the listbox with folder names. (See code in red for error location) ....

View Replies! View Related

Processing Data In Multiple Folders
I have a VB codes to process the data from a file that located in the folder from directory. This VB will copy the data from the file and paste it into new workbook by clicking the command button. I want to modify this code in order to make VB can process the data from multiple file from multiple folders. Previously, I just write the code for one folder only. So now, I want to modifiy the codes in order to expand more folders. All the results will be pasted into the same workbook.

Private Sub cmdStart_Click()
Application. ScreenUpdating = False
Set outbk = Workbooks.Add
Workbooks.Open "C:" & TextBox1.Value & "" & TextBox2.Value
Sheets("bin_trip").Select
With ActiveSheet

View Replies! View Related

FileListbox In The Form :: How To Display Folders?
I have inserted a FileListbox in the form.

it displays all files but not folders how can i display folders?

secondly i want to select and open the selected file.

View Replies! View Related

Loop Through Files In Dated Folders
each folder is named by date, for example a folder name may be 04-01-2008. Within each folder there is one xls file named daily.xls.
(this never changes)
In date order from the folder name, I need to open each daily.xls run my code, then close daily.xls and then open the next one etc until there are no more.

example path is C:4-01-2008daily.xls

View Replies! View Related

Hyperlink To Files Including Sub Folders
I am currently using the following code found in this post: Create Index Of Files In Folder

I am not familiar with VB at all. All I want to do is be able to index the sub-directories contained within the main directory. Is there a simple way to edit this code? ...

View Replies! View Related

List Folders Created By Procedure
I have the following procedure that creates sub folders for items in a named range (Room) in the parent folder (Pictures). The procedure works as expected; a sub folder is created for each item in the named range. What I'd like to do is give the User feedback as to what sub folders were created. This is the code I'm using:

View Replies! View Related

List Folders & Directories
I would like to create a list of folder/directories in the current directory. The only code I've been able to find lists all files in the current directory (not folders).

View Replies! View Related

Saving To/creating Folders In VBA
I'm trying to save a spreadsheet using VBA which will also check that the directory exists and create it if not, all based on cell values in my spreadsheet. When I run my code it comes up with Run Time Error 76: Path not found.

Dim fname As String, dname As String, dname2 As String
fname = ActiveSheet.Range("E2").Value
dname = ActiveSheet.Range("G2").Value
dname2 = ActiveSheet.Range("H2").Value
Windows("Register Template.xls").Activate
ChDir ("H:")
If Len(Dir(dname2, vbDirectory)) = 0 Then
MkDir dname2
End If
ChDir dname2
ActiveWorkbook.SaveAs Filename:=fname

View Replies! View Related

Macro That Cycles Through Sub Folders Within A Main Fold
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 Replies! View Related

Creating A Series Of Folders Named From A List
I would like to create a series of folders in explorer using a range of cells A1:A162 for the names of the folders. Wondering if there is a way that I can automate this using VBA versus doing it manually .

View Replies! View Related

Dir Folders And Subfolders Macro Writes Over It Self
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 Replies! View Related

Code To Pull Data From Workbooks Across Various Folders
I've seen a lot of code on how to pull data from workbooks in the same folder. However, I'm trying to pull the data in workbooks in various folders.

For examply, I have one folder, called "Master Files". In this folder is 12 folders, one for every month of the year. Within these folders, is one for each week, with the last day of the week being the name for the folder. In other words, it's like \Master FilesApril4-6-07data.xls

Now, I know how to get the data I need if all files are within the same folder. But how would I write a macro to run through all of those files when they're in separate folders?

View Replies! View Related

Open File By Searching Root And Sub Folders
is it possible to type a document name in a cell,then run a code that searches a root dir and sub folders, find the existing document and open it?? for example

cell: "D20"
filename: DYAZES-001
root dir: "I:IsolationDataBaseIsolationProcedures
subfolders: DryMillA, DryMillB, Despatch, Maintenance

View Replies! View Related

List Folders & Subfolders From Directory
how to list all the folders/subfolders (no files) of a specified directory?

I've seen a few examples of listing filenames from directories but I'm unsure how to just list folders/subfolders.

View Replies! View Related

Breakdown Of All Folders & Files In Directory
I am trying to specify all files and folders in a directory and the only help I found was the following thread: Create Index Of Files In Folder That works really well but, what I need is to specify all the folders and all the files under a directory, does any of you awsome hacks have an idea of how I can enhance the macro if the thread posted above?

View Replies! View Related

Consolidate Data From Multiple Workbooks From Different Folders/directory
I was searching through the internet to look for some help with regards to consolidating data from multiple workbooks from different directories into a single worksheet called Summary.

I would like to copy the entire worksheet from different workbooks in different directories into this workbook I like to call Summary. To manually do it I have to open the workbook I want to copy the worksheet from. Right click on the tab and select Move or Copy, select the sheet I want to copy and select the workbook I want to copy this sheet to. Repeat this process for all the workbooks.

The next step is to copy and paste from these sheets into a main summary sheet. The data range from these sheets are starting from row 13 onwards and they may be of different size between Column A to Column W.

For example the workbooks are coming from :

S:Area 1West.xls from worksheet Andy
S:Area 1East.xls from worksheet Bill
S:Area 1North.xls from worksheet John
S:Area 1North.xls from worksheet Albert etc...

View Replies! View Related

Copy And Paste Worksheets As Values To Named Folders
Am trying to copy 50 worksheets from One workbook, into separate existing files (overwriting previous file) in existing folders; once copied, destination files can not be linked to source file.

I'm able to copy the tabs into new workbooks, in the correct folders, but when trying to perform PasteSpecial in new books, original source file is also pasted over.

The parts of the file name and folder location are cell values located on each sheet.

...

For Each sheet In ThisWorkbook.Worksheets
sheet.Copy
Set workbook = ActiveWorkbook
With workbook.Sheets(1)
.UsedRange.Copy
.UsedRange.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With

View Replies! View Related

Move Files To Dynamic Folders Based On Cell Value
I am not so sure this can be done through a macro, but here is what I want.

I have a folder with say around 1000 excel workbooks. I need a macro to

1. Open each file in the directory specified
2. Read the contents of a particular cell, for ex, A1
3. Create a folder in the same directory with the value of A1
4. And, move the excel workbook itself to that folder.

5. Then it opens the next file
6. If the value of A1 is the same as that in the first workbook, it simply moves it to the already created folder, else it creates another folder with the new value of A1 and moves it there.

Hope I am not too confusing. Searching the forums does not give me an exact picture of how it can be done.

Note: I would also like an option where when I execute the macro, it should pop up with an option to select the folder in which the files are, since the files are not always saved in a static file path.

View Replies! View Related

Bulk Convert Strange File Format In Multiple Folders.
I have a series of files in *.ew7 format (i know its a random program)

I have some code which i can run which will simply open the relevant file and save it as xls.

The files are saved in multiple folders in format "yymm" eg: 0910 - oct 09.

inside each folder there is a ew7 file for each day in rthe format: "01ddmmyy" eg 01271009 = 27th oct 2009.

I have folders going back as far as may 2002 (in folder 0205) and i need a way to convert these all into xls format and save them in the same location.

View Replies! View Related

Moved Workbooks To Folders Now User Form Subsript Out Of Range
The attached workbook has a userform which was searching the same folder where this file is located for entries in columns "A" and "B" of files named in column "I" (workbook1, workbook2 etc).

I have this placed on a sharepoint and want to move the files into specific folders (folder1/workbook1, folder2/workbook2 etc.) so I can set them as view only folders to stop unuathorised editing.

I am now getting a "Subscript out of range" error after doing a search. I can see it's a problem with the file name as it's pulling it from text in a cell but can't work out!

View Replies! View Related

Macro- Code To Transfer Data From Multiple Workbooks Within Folders
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 Replies! View Related

Macro Error Message (object Variable Or With Block Variable Not Set)
When i try to run the code below i get the error message - object variable or with block variable not set-

Sub REFRESHXX()

'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing

With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With

'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar

'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1

End Sub

View Replies! View Related

Find Dates Macro: Object Variable With Block Variable Not Set
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select

End Sub

View Replies! View Related