Changing File Names Within Worksheets

Mar 25, 2009

my formula is as follows: =(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE)). I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.

ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name. So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets. I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup.

View 4 Replies


ADVERTISEMENT

Changing File Names In A Windows Directory

Nov 23, 2009

Using VBA, can anyone please advise if it is possible to change the name of a large number of files in a directory. I want to get each file name as a string, truncate the first few characters, then change the name to the new filename, all without opening any of the files whose names are to be changed.

View 9 Replies View Related

Macro To Batch Copy And Save Worksheets With File Names Listed In Cells?

Oct 23, 2013

I have a workbook that contains worksheets. They are listed as follows:

Sheet 1ABCDEFG

In cells A1 - A49 I have text. What I would like to do is to have a macro that I can run that will basically copy and save new workbooks with sheets A - G copied over and have the new workbook saved with the file name that I have denoted in cells A1 - A49 on Sheet 1. Also, the macro would ask me where I want to save the new Workbooks.

For example, if this were Sheet 1, Column A then the cells below would be the saved name of the new workbooks and the new workbooks would have Sheets A - G in themRed

Blue

Purple

Black

White

Yellow

Orange

Green

Gray

Brown

One more piece of information, the file that is being copied and saved is large (~80MB). If there is a macro that would allow me to simply "save as' the workbook and the Saved Workbooks would be named using the data in Sheet 1, that would work as well in case copying, pasting, then saving may take more time

View 1 Replies View Related

File Names :: File Renaming Each With The Names I Have On Another List

Jun 13, 2008

I have a task I would like some assistance with…

I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.

I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?

View 9 Replies View Related

Table Names Revert To Cell Names When File Is Reopened

Jan 4, 2013

I have a worksheet with many tables that I use in formulas.

I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.

Also, automatic copy of formulas/formatting is great.

But, what I really like about tables is the ability to use the naming conventions in formulas.

Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.

Example:

Code:

=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"

View 4 Replies View Related

Create Array Of File Names/sheet Names

May 1, 2008

Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

View 9 Replies View Related

Folder Names Instead Of File Names/macro

Dec 10, 2008

I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub

View 9 Replies View Related

Changing Order Of Names

Jul 20, 2009

Can this be done with a formula?:

Smith, John

changed to:

John Smith

View 11 Replies View Related

Changing Worksheet Names Anywhere Between 50-100

Jan 24, 2008

I would like to change the worksheets names (many, anywhere between 50-100) to a cell (A2) value in each of the worksheets.

View 9 Replies View Related

Changing Tab Names Using VBA Script

Dec 30, 2008

I'm creating a spread sheet which contains 52 tabs (1 for each week). Instead of manually naming each tab, I would like to create a VBA script that automatically renames each tab using the following naming convention, wk end 1-2, wk end 1-9, wk end 1-16, etc.

I'm not familiar with how to write VBA code and would like some assistance in writing the script correctly.

View 9 Replies View Related

Opening Excel File From List Of File Names Located In Single Sheet?

May 31, 2013

loop and range function to apply in the below code through which I can avoid writing code for all the rows.

I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).

I have created follwing code which opens the file and then runs a macro in it.

a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value

[Code].....

View 4 Replies View Related

Continual Changing Sheet Names

Nov 24, 2008

I want to capture data from another workbook each month where the sheet name that I need the data from changes each month. i.e. 0810, 0811, 0812, 0901. Are possible sheet names. I've tried to build this unsuccessfully into the my -

View 5 Replies View Related

Extract Data And File Names From File In Folder And Paste Into One Sheet?

Jan 13, 2013

The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.

how I could add to the code so that it also inserts the file name in column c?

It would make it easier to track the data in column B.

VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long

[Code].....

I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.

The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.

View 1 Replies View Related

Names Changing In Pivot Table Macro

Jul 19, 2013

I am running into an error when creating a macro to make a pivot table and sum certain columns. I am fairly certain this is due to the name changing to PivotTable2, 3, 4, etc. each time. Is there a way to make the name stagnent so when a table is created in the macro it doesn't error out the formulas? I have an example below.

Code:

Columns("A:D").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R1048576C4", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R1C1", TableName:="PivotTable5", DefaultVersion _
:=xlPivotTableVersion14

[Code] .........

View 2 Replies View Related

VBA Macro - VLookup From 2 Workbooks With Changing Names

Feb 17, 2014

I have a user which needs to run a vlookup daily. There is some formatting involved, especially with 2 exported workbooks...

So I wanted to make her a macro to do it. So she exports 2 workbooks where the names change based off the date range she runs the data for.

I was thinking I wanted her to close excel completely, then run them in a specific order and do the vlookups based of Index Numbers.

I can do the entire macro except for the syntax of the vlookup. I think if i write what I need, you guys could put it in proper syntax:

=Vlookup(RC[-10],Workbooks(2).Sheets(1).Range(A:A),1,false)

View 2 Replies View Related

Pivot Table - Changing Field Names

Jun 23, 2014

Using Pivot Table, I added a field to the "Values" field in order to give me a sum of the numbers. The title automatically changes to "Total", I am needing it to show "Total Invoice to OOM Delta". Is there a way to do this? I tried the Active Field option, but that's not working for some reason.

View 9 Replies View Related

Hyperlink Index To Changing Sheets Tab Names

Jan 11, 2008

I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -

View 5 Replies View Related

Generating A Repeating List Of Names From An Ever Changing Roster.

May 2, 2006

I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A is a relatively short list of constantly changing names. I need to put this list of names in a repeating fashion in Sheet 1 Col B. Ideally I would like for Sheet 1 to regenerate Col B each time the roster list is changed.

View 5 Replies View Related

Sheet Indirection. Refer To Sheets With Changing Names

Feb 25, 2008

I've been referencing sheets in my code directly with things like

Sheet1. Range(NamedRange)

however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to

#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2

'and then use these throughout the code....
SHEET_SC_CONFIG.Range(NamedRange)

(cannot reference by sheet name, as end user may change these)

View 6 Replies View Related

Link To External File With Changing File Name

Jun 12, 2007

Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...

View 9 Replies View Related

VBA Select Multiple Sheets Of Varied/Changing Sheet Names

May 18, 2006

How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher.
I have a group of 50+ workbooks which are all set up in the following format:

-Contains 30+ sheets.
-Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible.
-However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.

Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.

View 2 Replies View Related

Copy File Names From A Directory Into An File

Mar 11, 2007

Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?

View 9 Replies View Related

Changing The Color Of Worksheets?

Mar 9, 2014

I got 2 questions:

1) How can I change the font color and background color of multiple worksheets at the same time?

2) How can I change hyperlink color?

View 4 Replies View Related

Combining Names From Two Different Worksheets?

Jan 21, 2014

I have two worksheets...let's call them worksheet A and worksheet B. How can I get a list of people who are on worksheet A AND also on worksheet B? I would actually like those names on a separate worksheet.

View 6 Replies View Related

Run Code On Worksheets With Certain Tab Names

Aug 11, 2007

My Thread title should have read covert code TO run on all worksheets with same word in Name. I have had a look at some other pieces of code in this forum but they are quite the same as I want to do....I have tried a few things but each time I get an error ...so for sure I am not modifying the code quite right, I ahve been missing something for what I now want it to do. So I wonder if someone could Please review these two pieces of code...they of course are both for running on just one worksheet in a wookbook.

I would like them both be able to run, still as tow separete pieces of code as they are, on all and only worksheets that have in their worksheet name a key word in this case the word "Region" and that those worksheets all in a single workbook but the workbook does have other worksheets in it I dont want the code to run one on - but those worksheets do not have in their worksheet name the word "Region".

Sub test()
Dim r As Range, txt As String, ws1 As Worksheet, i As Long
Set ws1 = Sheets(1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 16 To 21
Again:
For Each r In ws1.Range(ws1.Cells(6, i), ws1.Cells(Rows.Count, i).End(xlUp))
If r.Value <> "" Then
If Not .exists(r.Value) Then
.Add r.Value, Nothing
Else
txt = txt & "," & r.Address(0, 0)
If Len(txt) > 245 Then
ws1.Range(Mid$(txt, 2)).EntireRow.Delete
txt = "": .RemoveAll: Goto Again
End If .............

View 9 Replies View Related

Loop Through Some Of Worksheets Without Using Customized Names

Nov 20, 2012

I have a piece of code from a form command button. its supposed to colect data and put it to appropriate cells.

VB:
FoundColumn = 0

For Each c In Sheet19.Range("A5:A33").Cells
If c.Value = student1.Value Then
'check if date allready exists
For Each cc In Sheet19.Range("c4:nc4").Cells
If Calendar1.Value = cc.Value Then

[Code] .....

I need this for Sheet19 and to 10 more worksheets like Sheet20, Sheet21 etc. I can manually copy paste the code and change the Sheet19 to whatever but it just does not seem the right thing to do. I tried:

VB:
For i = 1 To 6
naming = "Sheet" & i
MsgBox naming.Cells(1, 2).Value
Next i

but that gives an object required error.

View 2 Replies View Related

Consolitate Names And Address From Many Worksheets In To One

Apr 19, 2009

I need to create a macro that will create a mailing list from about 300 spread sheets in the same workbook. (I am proud of myself, I found a macro that helped me bring all 300 worksheets in to one workbook).

All worksheets are template style if B3 is company name in one worksheet it it the same on all worksheets. I have 15 cells I need to bring from each spread sheet into a master mailing list.

The big problem is, I only have the next 18 hours to do this and it is taking way too long to do it one at a time.

View 14 Replies View Related

Cross Referencing Names On Different Worksheets

Apr 23, 2009

I have three different sheets, each with a column of names, titled "list1, list2, list3." I also have a fourth list of names on a sheet titled "masterlist." I am wondering if there is any way I could cross check the three lists against the master list to see if any names appear on the master list but do not appear on any of the three lists. Is there any way I can cross reference the master list with each of these lists?

View 2 Replies View Related

Get Names Of All Worksheets In One Worksheet Without VBScript

Jun 18, 2012

Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?

Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.

View 5 Replies View Related

Adding Names To Worksheets During Merging?

Oct 31, 2013

I have an output in the form of multiple worksheets. I have found a solution to merge them all toghether into one workbook. But I would like the sheets to be named after the original file name.

Lets say my file is called 9252400.xlsx, i would then like the corresponding sheet in the workbook to be called 9252400

The code I am using for merging is:

Code:
Sub GetSheets()
Path = "C:Documents and SettingsDAJOMy DocumentsMD-CPHPI_Database-908315-3PlantInfo_Excel"

[Code].....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved