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
ADVERTISEMENT
Apr 15, 2013
I tried recording a macro to automate some tasks I perform every day between two workbooks. While in one I want to go get data from the other. The problem I'm having is the name of the workbooks I want to run this on changes every time I download my data into Excel. The files always start with the same name but the date gets appended to it. Is there a way to modify the macro to just work with the two books I've got open?
View 3 Replies
View Related
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
May 2, 2014
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
View 2 Replies
View Related
Jul 20, 2009
Can this be done with a formula?:
Smith, John
changed to:
John Smith
View 11 Replies
View Related
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
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
Sep 15, 2014
Is it possible to use defined names from other workbooks? I would like to use this defined name as part of a VBA code.
Example: ActiveChart.SeriesCollection(1).Values = Range(Name1)
where Name1 is a defined name from a different workbook
View 3 Replies
View Related
Feb 24, 2009
I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?
View 9 Replies
View Related
Feb 3, 2007
Each month I run 2 separate reports and have macros that break each report out by distributor. The end result is each spreadsheet creates a new tab and new workbook for each distributor’s information. Each spreadsheet has information for 30 distributors, so I end up with 60 new tabs and 60 new workbooks.
Example:
Spreadsheet one separates detailed sales information for:
Abc Company
123 Company
Misc. Company
Spreadsheet two separates summary sales information for:
Abc Company
123 Company
Misc. Company
Currently, I save each spreadsheets new workbooks in a different folder because each spreadsheet creates workbooks with the same distributor name. I then go back and open each spreadsheet with the same name and move a sheet from one workbook to the other and resave. Now I am trying to figure out the best way to merge these back together and am struggling with where to start.
I could keep opening each of the 2 new spreadsheets that are alike and copy or move sheets but with 30 new tabs to move to the matching spreadsheet it becomes time consuming. I’m hoping there’s a better way!
I’ve attached the Sales Detail Example and Sales Summary Example to see how the spreadsheets are being broken out but had to remove some data to trim down file sizes.
View 9 Replies
View Related
Jun 21, 2007
In one workbook, i have a sheet with date like this:
TITLE
Data......
TITLE2
Data2....
In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!
View 5 Replies
View Related
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
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
View Related
Feb 14, 2007
I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.
View 9 Replies
View Related
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
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
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
Mar 7, 2014
I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.
Is there a command line I can use, that will not prompt the user to save?
Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM
View 3 Replies
View Related
May 28, 2014
I would like to create a summary for the ordering history of each customers. The IT department will facilitate us to generate some raw data and I want to retrieve the data to the summary excel when I type the Ref No of the customer.
For example, I have the following raw data generated, in which the file name is "A123456":
Ref No
Name
Address
[Code]....
And I want to extract the data to the following summary. When I type "A123456" in the field "Ref Number" in this summary, it will automatically retrieve data from the corresponding raw file:
Trading Summary
Ref Number:
A123456
[Code]....
View 3 Replies
View Related
Jul 20, 2006
I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:
workbook(variable).activate
workbook.activate variable
workbook(variable).select
workbook.select variable
none of these work and I've tried a ton of other ways to get it to work but I just can't figure it out!
View 8 Replies
View Related
Oct 10, 2006
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList()
'The code below creates and names a new tab for each members info
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")
'extracts a list of member or distributor names
ws1.Columns("R:R"). AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("T1"), Unique:=True
r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area
Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r)
'adds the member name to the criteria area above
ws1.Range("U2").Value = c.Value
...............
View 9 Replies
View Related
Dec 2, 2006
This code loops through all columns in all sheets in all workbooks of a specified directory.
My copy paste to column C in "Loop Folder" works well thanks to this forum especially wigi
I want to paste the file name once in Column A and have it repeat for each new file opened. My code only does this for the first one as I simply can not fathom the coding
In column B I'd like to paste the sheet name once as they get opened.
I am struggling here as well on Sheets.Name coding ....
View 9 Replies
View Related
Aug 17, 2007
I have data spanning many files which are named with the date on which they were created (so there are 31 files for August).
For example:
PL080107, PL080207, PL080307, PL080407...)
I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.
I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.
I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!
View 5 Replies
View Related
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
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
May 9, 2013
So, We are using Excel 2002 & Excel 2007 and for some reason all of the workbooks that we have open decide to randomly change the fill colours of nearly all the cells. The only reason this is an issue is that a lot of my work envolves me sending reports to clients and from a business perspective this does not look professional at all.
To add to this, once the cells have changed colour, the original colour is no longer available to select.
View 3 Replies
View Related
Oct 25, 2012
I am trying to produce numerous workbooks based on a filtered name. I will attach a sample spreadsheet that has the data.
On the sheet we have engineer names. I basically want to filter them (not difficult with a macro) but then to copy the results to a new spreadsheet and save the workbook as the engineers name (ie J. Bloggs has 5 jobs so they are filtered and the results are dumped into a new workbook and then saved as J. Bloggs.xls). This will happen for all engineers.
I have though about doing it as a macro and I think that would give me the end result but we have around 20 engineers and these can sometimes go up and down. Is there anyway to do this automatically?
Test Cost report 1.xls
View 6 Replies
View Related
Oct 6, 2008
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).
View 6 Replies
View Related
Sep 2, 2007
I have some very tedious work to do in Excel:
table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .
S7999 u ee
S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
View 9 Replies
View Related
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
